Đánh giá lequocthai.com:
HLOOKUP là một hàm rất hữu ích để thực hiện tra cứu ngang, nhưng vì hầu hết các bảng mà chúng ta làm việc thường là dạng dọc, nên hàm này không được sử dụng phổ biến.
Nhiệm vụ của hàm HLOOKUP là tìm một giá trị trong hàng trên cùng của một bảng, sau đó trả về giá trị tương ứng trong cùng cột nhưng ở hàng mà bạn chỉ định.
Định nghĩa và Cú pháp của hàm HLOOKUP trong Excel
Microsoft Excel định nghĩa HLOOKUP là một hàm “tìm một giá trị trong hàng trên cùng của một bảng hoặc mảng giá trị và trả về giá trị trong cùng cột từ một hàng bạn chỉ định”.
Cú pháp của HLOOKUP trong Excel
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)- lookup_value: ở đây, “lookup_value” là giá trị cần tìm trong hàng trên cùng của bảng. “lookup_value” có thể là một giá trị, một tham chiếu hoặc một chuỗi văn bản.
- table_array: “table_array” là tham chiếu phạm vi hoặc tên phạm vi của một mảng giá trị, trong đó dữ liệu cần tra cứu.
- row_index_num: “row_index_num” là số thứ tự hàng trong “table_array” mà giá trị khớp sẽ được trả về. Khi “row_index_num” bằng 1, hàm sẽ trả về giá trị từ hàng trên cùng của “table_array”; tương tự, khi bằng 2, hàm trả về giá trị từ hàng thứ hai của “table_array”.
- range_lookup: đối số “range_lookup” nhận giá trị Boolean để chỉ định bạn muốn hàm tra cứu ngang trả về khớp chính xác hay khớp gần đúng. TRUE (đúng) – khớp gần đúng; FALSE (sai) – khớp chính xác.
Cách sử dụng HLOOKUP trong Excel
Xét bảng học sinh mẫu dưới đây:
Mục tiêu:
Trong ví dụ này, mục tiêu là lấy điểm tiếng Anh của Steve bằng hàm Horizontal Lookup.
lookup_value:
Vì cần tìm điểm của Steve, nên “lookup_value” sẽ là “Steve”.
table_array:
Tham chiếu bảng là A1:I4.
row_index_num:
Trong trường hợp này, hàng cần lấy là hàng thứ 4, vì điểm tiếng Anh nằm ở hàng 4.
range_lookup:
Chọn FALSE vì muốn khớp chính xác.
Kết quả công thức là 61.
Một số lưu ý quan trọng về HLOOKUP
- Horizontal Lookup thực hiện tra cứu không phân biệt chữ hoa/chữ thường, vì vậy “STEVE” và “steve” được coi là giống nhau.
- Khi dùng hàm HLOOKUP, “lookup_value” luôn phải nằm ở hàng trên cùng của “table_array”.
- Đối số “range_lookup” là tùy chọn; nếu bỏ qua, mặc định là TRUE (khớp gần đúng).
- Nếu HLOOKUP không tìm thấy “lookup_value” và “range_lookup” là TRUE, hàm sẽ trả về giá trị lớn nhất nhỏ hơn “lookup_value”.
- Giống như VLOOKUP, HLOOKUP hỗ trợ ký tự đại diện (* và ?) trong đối số “lookup_value” (chỉ khi “lookup_value” là văn bản).
- Nếu “range_lookup” là FALSE và HLOOKUP không tìm thấy “lookup_value” trong phạm vi đã cho, hàm sẽ trả về lỗi #N/A.
- Nếu “row_index_num” < 1, HLOOKUP trả về lỗi #VALUE!; nếu lớn hơn số hàng trong “table_array”, trả về lỗi #REF!.
5 ví dụ thực tế về HLOOKUP
Ví dụ 1
Tìm điểm tiếng Anh của học sinh có điểm môn Khoa học là 75.
Công thức:
=HLOOKUP(75,B2:I4,3,FALSE)
Kết quả: 40.
Giải thích:
- lookup_value = 75 (điểm môn Khoa học)
- table_array = B2:I4 (phạm vi bảng học sinh)
- row_index_num = 3 (hàng trả về là hàng điểm tiếng Anh)
- range_lookup = FALSE (khớp chính xác).
Ví dụ 2
Tìm điểm Toán của học sinh có tên bắt đầu bằng chữ “G”.
Công thức:
=HLOOKUP("G*",A1:I4,3,FALSE)Lưu ý: sử dụng ký tự đại diện “*”.
Giải thích:
- lookup_value = “G*” (tên bắt đầu bằng G)
- table_array = A1:I4
- row_index_num = 3
- range_lookup = FALSE.
Ví dụ 3
Có hai bảng, cần đưa điểm Lịch sử vào bảng đầu tiên.
Công thức:
=HLOOKUP(B1,$M$1:$T$2,2,FALSE)Sau đó kéo công thức sang các cột bằng tay kéo tự động (fill handle).
Ghi chú: nếu bạn thắc mắc các dấu “$” trong công thức, hãy tham khảo bài viết liên quan.
Giải thích:
- lookup_value = “B1” (tên học sinh)
- table_array = $M$1:$T$2
- row_index_num = 2 (hàng Lịch sử)
- range_lookup = FALSE.
Ví dụ 4
Tìm khối lượng nguyên tử (Atomic Mass) của Boron trong bảng nguyên tố.
Công thức:
=HLOOKUP(F1,B1:K4,3,FALSE)
Giải thích:
- lookup_value = “F1” (ô chứa Boron)
- table_array = B1:K4
- row_index_num = 3 (hàng khối lượng nguyên tử)
- range_lookup = FALSE.
Ví dụ 5
Tìm điểm Nhiệt chảy (Melting Point) của nguyên tố có khối lượng nguyên tử ≤ 15.
Công thức:
=HLOOKUP(15,B3:K4,2,TRUE)
Ghi chú: khi “range_lookup” = TRUE, nếu không có khớp chính xác, hàm sẽ trả về giá trị lớn nhất nhỏ hơn “lookup_value”.
Giải thích:
- lookup_value = 15 (khối lượng nguyên tử cần tìm)
- table_array = B1:K4
- row_index_num = 2 (hàng điểm Nhiệt chảy)
- range_lookup = TRUE (khớp gần đúng).
Trong ví dụ này, không có nguyên tố có khối lượng nguyên tử đúng bằng 15, nên hàm trả về giá trị gần nhất nhưng nhỏ hơn, là 14.01, và trả về điểm Nhiệt chảy tương ứng là -210.
Cách sử dụng HLOOKUP trong VBA
HLOOKUP trong VBA được gọi qua Application.WorksheetFunction.
Ví dụ 6
Viết chương trình VBA để lấy điểm của một học sinh trong tất cả các môn từ bảng dưới đây.
Mã nguồn:
Sub H_LOOKUP()
On Error GoTo ErrorHandler
Dim student As String
Dim Result As String
student = InputBox("Enter the student Name:")
If Len(student) > 0 Then
Result = "Science - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 2, False)
Result = Result & vbNewLine & "Maths - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 3, False)
Result = Result & vbNewLine & "English - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 4, False)
Result = Result & vbNewLine & "History - " & Application.WorksheetFunction.HLookup(student, ActiveSheet.Range("A1:I5"), 5, False)
MsgBox student & " has got following Marks:" & vbNewLine & Result
End If
Exit Sub
ErrorHandler:
If Err.Number = 1004 Then
MsgBox "Student Not found in the records!"
Else
MsgBox "Some Error Occurred"
End If
End Sub
Giải thích: Mã sử dụng nhiều công thức HLOOKUP để lấy điểm của học sinh trong các môn. Nếu tên nhập vào không tồn tại trong bảng, sẽ hiện thông báo “Student Not found in the records!”.
Đọc thêm:
MsgBox, InputBox, CONCATENATE trong Excel, hàm IF, câu lệnh On Error, VLOOKUP trong VBA
Sử dụng hàm ISNA cùng HLOOKUP
Khi “range_lookup” = FALSE và HLOOKUP không tìm thấy “lookup_value”, hàm sẽ trả về lỗi #N/A. Để ẩn lỗi này và hiển thị thông báo thân thiện, có thể kết hợp với ISNA.
Ví dụ 7
Tìm điểm Nhiệt chảy của nguyên tố có khối lượng nguyên tử 11.
Công thức:
=HLOOKUP(11,B2:K4,3,FALSE)
Để ẩn lỗi #N/A, dùng:
=IF(ISNA(HLOOKUP(11,B2:K4,3,FALSE)), "Value Not Found", HLOOKUP(11,B2:K4,3,FALSE))
Hàm ISNA trả về TRUE nếu công thức trả về lỗi #N/A.
Trả về nhiều giá trị từ một HLOOKUP duy nhất
Mặc dù HLOOKUP mặc định chỉ trả về một giá trị, bạn có thể tạo HLOOKUP dạng mảng để lấy nhiều bản ghi.
Ví dụ 8
Lấy điểm của Glen trong tất cả các môn.
Công thức:
=HLOOKUP("Glen",B1:I5,{1,2,3,4,5},FALSE)Lưu ý: đây là công thức mảng. Để nhập, chọn số ô bằng số hàng bạn muốn HLOOKUP trả về, dán công thức vào thanh công thức và nhấn Ctrl + Shift + Enter. Công thức sẽ được bao quanh bởi dấu ngoặc nhọn `{}`.
{=HLOOKUP("Glen",B1:I5,{1,2,3,4,5},FALSE)}Kết quả sẽ hiển thị các điểm của Glen.
Nếu vẫn chưa hiểu, xem hình động dưới:
Kết luận: Đó là toàn bộ nội dung về hàm HLOOKUP trong Excel. Bạn có thể tải bảng tính mẫu chứa tất cả các ví dụ ở đây. Hãy chia sẻ ý kiến và câu hỏi của bạn về chủ đề này.
