Cách sử dụng hàm HLOOKUP trong Excel cho người mới

Tương tự như người anh em VLOOKUP, hàm HLOOKUP trong Excel cũng là một hàm cực kì quan trọng và cần thiết mà người sử dụng Excel cần biết đến. Khi thành thạo HLOOKUP, bạn sẽ thấy mọi chuyện dường như trở nên đơn giản hơn rất nhiều dù là bảng tính có tới bao nhiêu dòng hay cột đi chăng nữa. Nếu bạn chưa biết rõ về hàm này thì chần chừ gì mà chưa đọc ngay bài viết này của ThuthuatOffice?

Hàm HLOOKUP trong Excel là gì?

Trong Excel, hàm HLOOKUP là hàm sẽ giúp bạn dò tìm giá trị như mong muốn từ một bảng tính và tự động điền sang một bảng tính mới hoặc một vị trí khác biệt. Điều này giúp bạn có thể tiết kiệm được vô số thời gian khi không cần phải tự mình dò tìm và copy từng dữ liệu một.

HLOOKUP là từ viết tắt của H – Horizontal – hàng ngang và LOOKUP – Look Up – Dò tìm trong tiếng Anh. Tức là nó sẽ dò tìm dữ liệu theo dạng hàng ngang, tuy nhiên khi trả về sẽ trả về theo dạng hàng dọc.

Sử dụng hàm LOOKUP trong Excel khi nào?

Hàm HLOOKUP trong excel được sử dụng khi bạn cần dò tìm dữ liệu trong một bảng, theo phạm vi theo hàng ngang (từ trái qua phải) và trả về dữ liệu tương ứng theo hàng dọc (từ trên xuống dưới).

Bạn có thể sử dụng HLOOKUP khi muốn tìm tên sản phẩm, đơn giá, số lượng,.. dựa trên mã có sẵn hoặc tìm tên nhân viên, xếp loại nhân viên dựa một vài tiêu chí cố định.

Công thức hàm HLOOKUP trong Excel

Hàm HLOOKUP có cú pháp như sau:

= HLOOKUP (Lookup_value, Table_array, Row_index_ num, Range_lookup)

Trong đó, bạn cần quan tâm tới:

• Lookup_value: Giá trị cần dò tìm
• Table_array: Vùng giới hạn dò tìm
• Row_index_num: Số thứ tự của hàng lấy dữ liệu trong bảng cần dò tìm, tính từ trên xuống dưới
• Range_lookup (TRUE hoặc FALSE): tìm kiếm chính xác hay tương đối trong bảng giới hạn. Có thể bỏ qua, và khi bỏ qua thì Excel mặc định là TRUE.

Với Range_lookup =TRUE: Excel sẽ trả về kết quả dò tìm tương đối.

Với Range_lookup = FALSE: Excel sẽ trả về kết quả dò tìm chính xác. Nếu không tìm thấy thì Excel sẽ trả về kết quả #N/A.

Cách sử dụng hàm HLOOKUP trong Excel

Cách sử dụng hàm HLOOKUP và LEFT

Cú pháp của hàm LEFT là:

= LEFT(text; n)

Đây là hàm sẽ giúp bạn lấy được kí tự thứ n tính từ bên trái của văn bản trong phần text. Khi kết hợp hàm HLOOKUP và LEFT, công việc điền dữ liệu của bạn sẽ diễn ra nhẹ nhàng và nhanh chóng hơn bao giờ hết!

Chẳng hạn, bạn cần điền tên ngành khi biết mã lớp. Mã lớp bao gồm cả phần chữ và số, nhưng mà ngành chỉ bao gồm phần chữ. Khi đó bạn sẽ cần đến sự kết hợp của HLOOKUP và LEFT.

Công thức bạn điền vào sẽ là:

=HLOOKUP(LEFT(C4;4);$A$12:$F$13;2;FALSE)

Trong đó:

• C4: địa chỉ ô cần tách lấy ký tự
• 4: cần tách đến ký tự thứ mấy tính từ trái qua
• LEFT(C4;4): tách lấy 4 ký tự đầu tiên (từ trái qua) của dữ liệu trong ô Mã lớp
• $A$12:$F$13: vùng dữ liệu cần tìm kiếm
• 2: số thứ tự của dòng Tên ngành cần điền vào. Dòng Mã ngành sẽ được tính là 1 nên dòng Tên ngành sẽ là 2
• FALSE: dữ liệu điền vào cần chính xác tuyệt đối

Kết quả nhận được trong ô:

Copy công thức đến các dòng còn lại và bạn đã hoàn thành:

Hàm HLOOKUP kết hợp hàm IF

Khi bạn kết hợp HLOOKUP và IF, bạn sẽ dễ dàng có thể xếp loại và điền thông tin dựa trên điều kiện mà bạn đưa ra.

Chẳng hạn, bạn cần đánh giá xem học sinh của bạn đạt hay không đạt trong kì thi các cấp với dữ liệu ở hai bảng sau đây.

Khi đó, nếu học sinh thi cấp Trường và đạt từ 500 điểm trở lên, học sinh sẽ được điền chữ “Đạt” trong cột đánh giá, ngược lại, học sinh sẽ được điền chữ “Không đạt”. Tương tự như vậy cho kỳ thi cấp Thành phố và Tỉnh.

Công thức điền vào cột Đánh giá sẽ là:

=IF(E4>=HLOOKUP(D4;$C$12:$F$13;2;FALSE);”Đạt”;”Không đạt”)

Trong đó,

• E4: địa chỉ ô tính chứa điểm của học sinh
• D4: địa chỉ ô tính chứa điều kiện cần dò tìm, trong trường hợp này là tên kỳ thi
• $C$12:$F$13: địa chỉ vùng dữ liệu cần dò tìm
• 2: số thứ tự của dòng dữ liệu cần điền so với dữ liệu cần tìm. Dòng tên kỳ thi là dòng cần tìm, nó sẽ là dòng thứ 1, vậy nên dòng điểm đậu là dòng cần điền sẽ là ở vị trí thứ 2
• FALSE: dữ liệu cần tìm phải chính xác tuyệt đối
• “Đạt”: nếu điều kiện đúng thì ghi chữ “Đạt”. Trong trường hợp này, nếu điểm của học sinh lớn hơn hoặc bằng với số điểm đậu của kỳ thi tương ứng thì học sinh sẽ đạt
• “Không đạt”: nếu điều kiện sai thì ghi chữ “Không đạt”. Trong trường hợp này, nếu điểm của học sinh nhỏ hơn số điểm đậu của kỳ thi tương ứng thì học sinh sẽ không đạt

Kết quả nhận được ở ô tính đầu tiên:

Copy công thức đến các dòng còn lại để hoàn thành điền thông tin cho cột Đánh giá:

Lưu ý khi áp dụng hàm HLOOKUP tìm kiếm giá trị

Khi sử dụng hàm HLOOKUP trong Excel nhằm mục đích tìm kiếm giá trị, bạn cần lưu ý một số điều sau để đảm bảo dữ liệu tìm kiếm nhận được đúng như mong muốn và không xảy ra bất kì lỗi nào.

• Đảm bảo dữ liệu ở ô cần tìm và dòng đầu tiên trong bảng dữ liệu ở cùng một dạng (văn bản hoặc số).
• Đảm bảo khóa vùng dữ liệu tìm kiếm để địa chỉ không bị nhảy trong quá trình copy công thức.
• Đảm bảo lựa chọn đúng thứ tự của dòng cần điền so với dòng cần tìm. Dòng đầu tiên trong vùng dữ liệu bạn chọn tìm kiếm sẽ là 1.

Ví dụ hàm HLOOKUP trong Excel

Sử dụng hàm HLOOKUP trong Excel để trích bảng lương cho nhân viên

Giả sử, bạn có một bảng dữ liệu tổng và bạn muốn trích ra dữ liệu của một vài nhân viên như thế này:

Khi đó, bạn nên sử dụng hàm HLOOKUP để thực hiện điền các thông tin vào bảng dữ liệu mới thay vì copy từng dòng một. Chẳng hạn, nếu muốn điền vào ô số ngày công, bạn sẽ áp dụng công thức sau:

= HLOOKUP(B13;$A$1:$I$7;6;FALSE)

Trong đó:

• B13: là địa chỉ ô của dữ liệu cần tìm kiếm. Trong trường hợp này là tên của nhân viên.
• $A$1:$I$7: là địa chỉ vùng dữ liệu bạn cần tìm kiếm. Sau khi lựa chọn xong vùng dữ liệu, bạn hãy nhấn F4 để khóa vùng dữ liệu này lại. Khi đó ký hiệu $ sẽ hiện lên và dù bạn có copy công thức xuống các dòng dưới thì vùng dữ liệu này cũng không bị thay đổi.
• 6: là số thứ tự của dòng cần điền kí tự kể từ dòng có chứa dữ liệu cần tìm trong bảng tính. Trong trường hợp này, dữ liệu cần tìm là họ tên nên dòng họ tên trong bảng tính sẽ tính là 1, dòng cần điền dữ liệu là dòng Số ngày công, tính từ dòng họ tên xuống là dòng thứ 6, nên dữ liệu này sẽ cần điền số 6.
• FALSE: vì thông tin cần điền phải chính xác tuyệt đối nên tại đây điền FALSE.

Tương tự như vậy với phần Lương, bạn chỉ cần thay đổi số 6 thành số 7, tức là phần số thứ tự của dữ liệu cần điền là ở dòng Lương thay vì dòng Số ngày công. Copy công thức này tới các ô còn lại để hoàn thành bảng tính:

Sử dụng hàm HLOOKUP trong Excel để xếp loại học sinh dựa trên điểm

Chẳng hạn bạn cần xếp loại học lực của học sinh dựa trên điểm trung bình, bạn cần chuẩn bị một bảng dữ liệu chứa các con điểm như bên dưới:

Lúc này, trong cột xếp loại, bạn sẽ sử dụng công thức như sau:

= HLOOKUP(D4;$B$11:$F$12;2;TRUE)

Trong đó:

• D4: là địa chỉ ô của dữ liệu cần tìm kiếm. Trong trường hợp này là số điểm của học sinh.
• $B$11:$F$12: là địa chỉ vùng dữ liệu bạn cần tìm kiếm. Sau khi lựa chọn xong vùng dữ liệu, bạn hãy nhấn F4 để khóa vùng dữ liệu này lại. Khi đó ký hiệu $ sẽ hiện lên và dù bạn có copy công thức xuống các dòng dưới thì vùng dữ liệu này cũng không bị thay đổi.
• 2: là số thứ tự của dòng cần điền kí tự kể từ dòng có chứa dữ liệu cần tìm trong bảng tính. Trong trường hợp này, dữ liệu cần tìm là điểm nên dòng điểm trong bảng tính bên dưới sẽ tính là 1, dòng cần điền dữ liệu là dòng xếp loại, tính từ dòng điểm xuống là dòng thứ 2, nên dữ liệu này sẽ cần điền số 2.
• TRUE: vì bảng tính bên dưới của bạn đã được chia khoảng nên dữ liệu cần điền chỉ cần chính xác tương đối. Nghĩa là nếu điểm trên 0 và dưới 5, Excel sẽ điền Yếu, trên 5 và dưới 7, Excel sẽ điền Trung bình,…

Copy công thức xuống các dòng còn lại và kết quả là:

Hàm HLOOKUP và hàm VLOOKUP

Cả HLOOKUP và VLOOKUP đều có mang tính chất dùng để tìm kiếm. Tuy nhiên hai hàm này đều có những sự khác nhau mà bạn cần phân biệt để dễ dàng sử dụng cho công việc của mình.

Sau khi đã phân biệt được kĩ càng hai hàm này, đảm bảo công việc với bảng tính của bạn sẽ trở nên dễ dàng hơn bao giờ hết.

Các lý do chính khiến hàm HLOOKUP không chạy được

Khi sử dụng hàm HLOOKUP trong Excel, sẽ có đôi lúc bạn gặp phải trường hợp hàm không chạy được. Trong đó, chủ yếu là lỗi xảy ra bởi 3 nguyên nhân sau:

• Sai định dạng

Lỗi này tức là khi ô tính mà bạn trỏ địa chỉ tới trong công thức đang chứa dữ liệu ở dạng số nhưng dòng đầu tiên lại chứa dữ liệu ở dạng chữ (hoặc ngược lại). Để khắc phục, bạn có thể sử dụng kèm hàm TEXT trong công thức hoặc chỉnh sửa lại giá trị các ô tính cho trùng khớp.

• Thừa dấu cách ở cuối cùng trong điều kiện tìm kiếm

Nếu bạn đã sử dụng hàm HLOOKUP trong Excel và kiểm tra thật kĩ lưỡng để không có lỗi nào xảy ra nhưng kết quả nhận được vẫn sai hoặc không có kết quả nào thì nguyên nhân có thể nằm ở việc dữ liệu của bạn đang thừa dấu cách ở cuối. Có nghĩa là, thay vì là “Thanh Minh” thì dữ liệu trong ô tính lại là “Thanh Minh “, điều này dẫn đến việc Excel không thể tìm kiếm được chính xác dữ liệu như bạn mong muốn.

Để khắc phục lỗi này, bạn chỉ cần kiểm tra để chắc chắn 100% ô tính của bạn không hề có dấu cách nào ở cuối. Hoặc sử dụng công cụ Text To Columns ở thẻ Data để xóa toàn bộ khoảng cách.

• Không khóa bảng dữ liệu tìm kiếm

Việc khóa bảng dữ liệu tìm kiếm là điều đặc biệt quan trọng khi bạn sử dụng hàm VLOOKUP hay HLOOKUP trong Excel. Bởi Excel có tính năng tự động thay đổi địa chỉ ô khi bạn copy công thức xuống các dòng khác, nên nếu bạn không khóa bảng dữ liệu, khi copy công thức thì Excel sẽ thay đổi giá trị địa chỉ của bảng dò tìm này. Điều này dẫn đến việc bạn không tìm ra được giá trị như mong muốn hoặc giá trị bị sai hoặc gặp lỗi #VALUE!.

Để khóa bảng dữ liệu, trong công thức, sau khi lựa chọn vùng tìm kiếm, bạn chỉ cần nhấn F4 trên bàn phím (hoặc một số laptop là tổ hợp Fn + F4).

Xem thêm:

• Cách SORT trong Excel
• Các phím tắt trong Excel

Đừng quên Like, Share nếu thấy những cách sử dụng hàm HLOOKUP trong Excel này hữu ích nhé! Nếu ghé thăm ThuthuatOffice thường xuyên, bạn cũng sẽ có cơ hội được biết thêm cực kỳ nhiều thủ thuật hấp dẫn khác.

• Lỗi #VALUE! trong Excel là gì và cách khắc phục nhanh chóng

• Cách cố định ô trong Excel để cuộn và đối chiếu dễ dàng hơn

• Cách thêm dấu tích trong Excel mọi phiên bản 2007, 2010, 2013, 2016

• Tổng hợp 1001 thủ thuật gộp ô trong Excel mà ai cũng nên biết

• Cách SORT trong Excel từ cơ bản đến nâng cao

• 4 cách đánh số thứ tự trong Excel, có cách hay hơn kéo chuột

• 1001 bí quyết vẽ biểu đồ trong Excel mà ai cũng nên biết