Công thức Hàm hlookup trong Excel và cách sử dụng kèm ví dụ

Hàm HLOOKUP trong Excel là một trong những hàm thuộc nhóm hàm tìm kiếm, dò tìm hay được sử dụng trong công việc, học tập. Hàm HLOOKUP có thể kết hợp được với rất nhiều hàm Excel cơ bản khác bởi tính đa dạng và linh hoạt trong thành phần công thức hàm. Vậy cách sử dụng và ý nghĩa của hàm HLOOKUP như thế nào? Hãy cùng Tin Học Thành Luân tìm hiểu trong bài viết ngay dưới đây nhé!

Giống với hàm VLOOKUP, INDEX, MATCH về chức năng dò tìm nhưng khác về cách thức hoạt động nên có thể đôi với nhiều bạn chuộng sử dụng hàm VLOOKUP hơn là hàm HLOOKUP. Nhưng trong nhiều trường hợp mà hàm HLOOKUP lại cho thấy khả năng ưu việt của mình.

Giới thiệu về Hàm HLOOKUP trong Excel

Hàm HLOOKUP trong Excel là hàm dò tìm dữ liệu trong bảng theo hàng ngang và lấy giá trị theo cột. Chữ H trong tên hàm HLOOKUP là “Horizontal – Ngang”, hàm HLOOKUP được sử dụng trong các bảng tính tìm tên nhân viên, tìm đơn giá, tên sản phẩm,..  từ mã nhân viên, mã sản phẩm,…

Hàm HLOOKUP trong Excel cũng hỗ trợ các kiểu tìm kiếm khác nhau như kiểu tìm kiếm tương đối. Nếu bạn muốn tìm kiếm giá trị gần đúng, bạn có thể sử dụng kiểu tìm kiếm tương đối bằng cách thay đổi thành phần cuối của hàm về 1 hoặc TRUE.

Cú pháp và cách sử dụng hàm HLOOKUP trong Excel

Cú pháp: HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

• Lookup_value: Đây là giá trị mà bạn muốn tìm kiếm trong bảng dữ liệu, có thể là một giá trị, tham chiếu hoặc chuỗi văn bản.
• Table_array: Đây là phạm vi dữ liệu mà bạn muốn tìm kiếm giá trị trong đó.
• Row_index_num: Đây là số thứ tự hàng trong phạm vi dữ liệu mà bạn muốn lấy ra giá trị.
• Range_lookup: Đây là một giá trị tùy chọn để xác định xem bạn có muốn tìm kiếm chính xác hay tương đối. Nếu range_lookup = TRUE hoặc bị bỏ trống, hàm HLOOKUP sẽ hiểu bạn tìm kiếm tương đối. Ngược lại, nếu range_lookup = FALSE, hàm HLOOKUP sẽ tìm kiếm chính xác.

Ví dụ đơn giản cách sử dụng hàm HLOOKUP

Ở ví dụ này, mình muốn nêu ra 1 ví dụ đơn giản để bạn hiểu hơn về cách sử dụng hàm HLOOKUP trước rồi sau đó chúng ta sẽ qua những ví dụ nâng cao hơn ở những ví dụ dưới.

Giả sử, chúng ta có một bảng kê tiền thuê máy tính của một shop đang cần tính tiền thuê theo giờ và theo phút.

Nhìn vào ví dụ trên, vận dụng từ lý thuyết vào thực tế bài toán, bước đầu tiên bạn phải tìm ra được thành phần Lookup_value trong bảng và đó là cột Mã Số. Tiếp theo xác định được vùng Table_array là vùng mang cái Lookup_value kia đi dò tìm trong đó. Ở đây chúng ta sẽ xác định Table_array chính là BẢNG 1 và khi chúng ta chọn thì các bạn chỉ cần chọn phạm vi tính từ cột có Mã Số I sang bên phải.

Số thứ tự hàng – Row_index_num phụ thuộc vào đơn giá tính trên 1 giờ hay 1 phút mà các bạn chọn 2 hoặc 3 tính từ hàng chứa Mã Số. Tìm kiếm hiện tại là tìm kiếm chính xác nên các bạn sẽ điền vào thành phần công thức cuối sẽ là 0 hoặc FALSE. Chú ý ở ví dụ này các bạn không chỉ dò tìm đơn giá mà kết hợp thêm việc tính tiền thuê luôn.

Do vậy, bạn cần phải tách từng phần ra trước, đầu tiên là dùng hàm HLOOKUP để lấy ra đơn giá thuê 1 giờ:

Công thức: =HLOOKUP(A4, $I$4:$K$5, 2, 0)

Trong đó:

• A4: Mã Số mang đi dò tìm trong BẢNG 1
• $I$4:$K$5: Vùng dữ liệu Đơn giá/ 1 giờ
• 2: Số thứ tự hàng cần lấy giá trị
• 0: Chỉ số tìm kiếm chính xác

Tiếp theo, chúng ta sẽ viết công thức lấy đơn giá tính theo phút như sau:

Công thức: =HLOOKUP(A4, $I$4:$K$6, 3, 0)

Sau khi đã có 2 công thức như trên, chúng ta tiến hành nhân số giờ với đơn giá và số phút với đơn giá rồi ghép lại thành công thức hoàn chỉnh:

Công thức: =HLOOKUP(A4, $I$7:$K$9, 3, 0) * D4 + HLOOKUP(A4, $I$7:$K$8, 2, 0) * C4

Các bạn cần chú ý cố định công thức tại vùng BẢNG 1 và lưu ý chọn vùng cho đúng, ví dụ bạn lấy giá trị đơn giá theo phút nhưng vùng dữ liệu bạn chỉ kéo tới hàng Đơn giá theo giờ thì chắc chắn kết quả sẽ về 0. Và kết quả của công thức trên như sau:

Ví dụ bài toán xếp hạng

Có lẽ dạng bài toán này rất hay gặp trong công việc của người quản lý ví dụ như giáo viên, người quản lý trong doanh nghiệp. Ở ví dụ này, mình sẽ lấy 1 ví dụ về xếp hạng học lực cho sinh viên dựa trên một bảng xếp hạng học lực mẫu.

Hàm HLOOKUP trong Excel có thể xếp hạng khi dùng chỉ số dò tìm tương đối áp dụng trong bảng dữ liệu dạng chia khoảng như ảnh trên. Nhìn vào ảnh trên ta có thể thấy điểm từ 0 đến 5 xếp hạng Yếu, từ 5 tới 6 xếp hạng Trung bình, từ 6 tới 8 xếp hạng Khá, từ 8 trở lên là Giỏi.

Quy tắc như sau: Khi ĐTB của sinh viên Lý Mạnh là 3.67 dò tìm trong Bảng Xếp Hạng Mẫu nó sẽ lấy giá trị trong khoảng tương đối với nó nhất. Ví dụ 3.67 nằm trong khoảng từ 0 tới 5 và trả về kết quả là Yếu. Chúng ta sẽ có công thức như sau:

Công thức: =HLOOKUP(G3, $B$13:$E$14, 2, 1)

Trong đó:

• G3: Điểm trung bình của sinh viên mang đi dò tìm
• $B$13:$E$14: Vùng dữ liệu trong Bảng Xếp Hạng Mẫu
• 2: Số thứ tự hàng ở đây là hàng Xếp hạng
• 1: Chỉ số tìm kiếm tương đối, bạn có thể thay bằng TRUE

Chúng ta sẽ có kết quả sau khi kéo công thức xuống như sau:

Ví dụ hàm HLOOKUP kết hợp hàm IF

Khi kết hợp hàm HLOOKUP với hàm IF trong Excel, bạn có khả năng tạo ra những công thức phức tạp hơn để thực hiện các phân tích dựa trên các điều kiện. Hàm HLOOKUP và hàm IF có thể hoán đổi vị trí cho nhau ví dụ khi hàm IF lồng hàm HLOOKUP, khi thì hàm HLOOKUP lồng hàm IF. Chúng ta sẽ đi tìm hiểu ví dụ dưới đây để thấy rõ hơn về sự kết hợp của 2 hàm này.

Giả sử chúng ta có Bảng Doanh Thu Quán Game như ảnh trên yêu cầu tính tiền từng khách hàng khi sử dụng dịch vụ tại quán game này.

Phân tích một chút về bài toán trên, Tính tiền = Đơn giá * Số lượng trong đó Đơn giá được lấy từ Bảng Đơn Giá phụ thuộc vào Loại Phòng và Dịch Vụ. Ví dụ tính tiền cho khách Lê Trung Loại Phòng là Phòng VIP sử dụng Dịch vụ Mì gói với số lượng là 1. Chúng ta có thể thấy ngay được đơn giá là 15000 và Tính tiền sẽ là 15000.

Nếu dịch xuôi sẽ như sau: Khách Lê Trung sử dụng Phòng VIP thì tính tiền số lượng Mì gói đã dùng. Vậy nếu khách hàng sử dụng Phòng thường thì như thế nào, chắc chắn rồi bạn sẽ phải cho điều kiện là Phòng thường rồi mới tìm tới đơn giá và tính tiền được. Và chúng ta sẽ có công thức Hàm IF làm nhiệm vụ so sánh điều kiện Phòng VIP và hàm HLOOKUP làm nhiệm vụ dò tìm đơn giá trong Bảng Đơng Giá kia.

Công thức: =IF(C4 = $H$8, HLOOKUP(D4, $J$7:$L$8, 2, 0) * E4, HLOOKUP(D4, $J$7:$L$9, 3, 0) * E4)

Do chỉ có 2 điều kiện là Phòng VIP và Phòng thường nên chúng ta sẽ có 1 câu lệnh IF như trên:

• C4=$H$8: Kiểm tra xem Loại phòng là Phòng thường hay Phòng VIP
• HLOOKUP(D4,$J$7:$L$8,2,0): Dò tìm Đơn giá sử dụng Phòng VIP ở Bảng Đơn Giá
• HLOOKUP(D4,$J$7:$L$9,3,0): Dò tìm Đơn giá sử dụng Phòng thường ở Bảng Đơn Giá

Ví dụ hàm HLOOKUP kết hợp hàm xử lý chuỗi

Hàm HLOOKUP có thể kết hợp với hàm xử lý chuỗi như hàm LEFT, RIGHT, MID, theo như kinh nghiệm của mình thì các hàm xử lý chuỗi này rất hay được sử dụng kết hợp với hàm HLOOKUP. Hàm xử lý chuỗi sẽ dùng cho việc tách mã sản phẩm, mã sinh viên,..sau đó hàm HLOOKUP sẽ đặt kết quả tách chuỗi đó vào Lookup_Value rồi mới đi dò tìm trong bảng dữ liệu.

Ở ví dụ này, mình sẽ lấy 1 ví dụ mà mình nghĩ các bạn sẽ gặp rất nhiều trong công việc, học tập, hãy cùng xem ví dụ dưới đây nhé!

Giả sử ta có Bảng Kết Quả Tuyển Sinh cần điền Tên Ngành dựa vào Bảng Ngành Học.

Nhìn vào bảng trên dễ nhận thấy ở cột Mã Số Ngành-Ưu tiên có ký tự đầu trùng với các giá trị ở hàng thứ nhất Bảng Ngành học. Để lấy được ký tự đầu của Mã Số Ngành ta sẽ dùng hàm LEFT – Hàm lấy ký tự bên trái chuỗi. Và khi chúng ta lấy được ký tự đầu từ cột đó xong chúng ta sẽ sử dụng hàm HLOOKUP để dò tìm trong Bảng Ngành Học để lấy ra Tên Ngành. Chúng ta có công thức như sau:

Công thức: =HLOOKUP(LEFT(C3, 1), $C$8:$D$9, 2, 0)

Giải thích:

• LEFT(C3,1): Lấy ký tự đầu của cột Mã Số Ngành-Ưu tiên
• $C$8:$D$9: Vùng dữ liệu Bảng Ngành Học
• 2: Số thứ tự hàng Tên Ngành
• 0: Chỉ số dò tìm chính xác

Bạn có thể tham khảo kết quả dưới đây, do không có Mã ngành C nên sẽ bị lỗi #N/A, để xử lý lỗi này chúng ta có thể sử dụng hàm IFERROR để thay lỗi #N/A bằng thông báo “Không có Mã ngành C”.

Công thức xử lý lỗi bằng thông báo: =IFERROR(HLOOKUP(LEFT(C3, 1), $C$8:$D$9, 2, 0), “Không có Mã ngành C”)

Trong bài viết này, chúng ta đã tìm hiểu về Công thức Hàm hlookup trong Excel và cách sử dụng nó. Hàm HLOOKUP là một trong những hàm thường được sử dụng trong Excel để tìm kiếm và trích xuất dữ liệu từ bảng dữ liệu dựa trên một giá trị được cung cấp. Điểm mạnh của Hàm HLOOKUP là khả năng truy cập vào các bảng lớn và trích xuất dữ liệu một cách nhanh chóng và chính xác.

Hy vọng thông qua bài viết này, bạn đã có cái nhìn cụ thể về Công thức Hàm HLOOKUP trong Excel và cách sử dụng nó. Hãy áp dụng kiến thức này vào công việc hàng ngày để tăng cường hiệu suất làm việc của mình trong Excel. Đừng quên đọc nhiều bài viết chất lượng khác tại mục Excel cơ bản trên trang web tinhocthanhluan.com nhé!