Hàm HLOOKUP và VLOOKUP là hàm dò tìm khá phổ biến trong Excel. Đôi khi bạn sẽ bị nhầm lẫn khi sử dụng các hàm này. Bài viết dưới đây Azgad Edu sẽ hướng dẫn cho bạn chi tiết cách sử dụng HLOOKUP dò tìm giá trị trong bảng tính Excel nhé!
Giới thiệu về hàm HLOOKUP trong Excel
Công thức hàm HLOOKUP
= HLOOKUP ( Lookup_value , Table_array , Row_num, [Range_lookup])
Ý nghĩa:
=HLOOKUP(Giá trị cần dò, Bảng giá trị dò, Số thứ tự hàng cần lấy, Phạm vi tìm kiếm).
- Lookup_value (Giá trị cần dò): Giá trị đem dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
- Table_array (Bảng giá trị dò): Bảng chứa giá trị cần dò tìm (khi chọn không quét tiêu đề).
- Row_num (Số thứ tự hàng cần lấy): Số thứ tự của hàng cần lấy dữ liệu trong bảng chứa giá trị dò, tính từ trái qua phải.
- Range_lookup (Phạm vi tìm kiếm): Nếu là True (1): dò tìm tương đối, False (0): dò tìm chính xác tuyệt đối.
Lưu ý:
- Nếu Range_lookup là TRUE (1) – dò tìm tương đối: Trong trường hợp không tìm được giá trị chính xác, Excel sẽ trả về kết quả gần sát nhất với giá trị đem dò.
- Nếu Range_lookup là FALSE (0) – dò tìm chính xác: Trong trường hợp không tìm được giá trị chính xác, Excel sẽ trả về kết quả #NA (lỗi không tìm thấy giá trị cần dò).
- Nếu bỏ qua thì Excel sẽ mặc định Range_lookup là TRUE (1) – dò tìm tương đối.
Ứng dụng hàm HLOOKUP trong Excel
Hàm HLOOKUP được sử dụng để dò tìm dữ liệu trong 1 bảng cho trước và các dữ liệu cần tìm trong bảng này nằm trên 1 hàng ngang (kết quả trả về nằm trên hàng dọc tương ứng). Để tìm kiếm giá trị nằm trên hàng dọc (cột) ta sử dụng hàm VLOOKUP.
Hướng dẫn chi tiết hàm HLOOKUP
Sử dụng HLOOKUP tìm giá trị chính xác tương đối
Để giúp bạn hiểu rõ hơn về công thức HLOOKUP với yêu cầu tìm kiếm giá trị chính xác tuyệt đối, Azgad Edu sử dụng bảng thông tin dưới đây:
Ví dụ hàm tìm kiếm HLOOKUPVí dụ 1: Xếp loại cho các thí sinh dựa vào Bảng xếp loại trên.
Tại ô E4 ta nhập công thức sau: =HLOOKUP(E4,$C$11:$F$12,2,1).
Nhập công thức
Giải thích:
Cấi trúc HLOOKUP sẽ dò tìm số điểm trung bình tại ô E4 trong Bảng xếp loại C11:F12 theo thứ tự từ trái qua phải. Phạm vi tìm kiếm là 1 (có thể thay thế bằng TRUE) có ý nghĩa khi tìm thấy giá trị gần bằng với giá trị của ô E4, Excel sẽ trả về kết quả xếp loại tương ứng nằm ở hàng 2 của Bảng xếp loại.
Lưu ý:
- Tại bảng giá trị dò (Bảng xếp loại) ta không quét tiêu đề.
- Để sao chép công thức xuống các giá trị ở phía dưới, ta cố định bảng giá trị dò lại bằng cách nhấn nút F4 trên bàn phím.
Để sao chép kết quả cho các ô ở dưới, ta có thể dùng 1 trong hay cách sau:
- Cách 1: Bôi đen các ô F4 đến F8 và nhấn tổ hợp phím Ctrl + D.
- Cách 2: Để chuột vào góc phải cuối ô F4 đến khi xuất hiện dấu + và kéo xuống dưới để sao chép công thức.
Ta nhận được kết quả như sau:
Kết quả nhận được
Hàm HLOOKUP tìm giá trị tuyệt đối
Ví dụ 2: Lập công thức xác định tiền thưởng cho từng nhân viên. Biết rằng, mức phụ cấp dựa theo chức vụ được quy định theo bảng quy định tiền thưởng.
Ví dụ hàm tìm kiếm trong Excel
Tại ô E2 ta nhập công thức sau: =HLOOKUP(C3,$C$10:$E$11,2,0).
Nhập công thức
Giải thích:
Dò tìm chức vụ GD tại ô C3 trong Bảng quy định tiền thưởng C10:E11 theo thứ tự từ trái qua phải. Phạm vi tìm kiếm là 0 (có thể thay thế bằng FALSE) có ý nghĩa khi tìm thấy giá đúng tuyệt đối với giá trị của ô C3, Excel sẽ trả về kết quả tiền thưởng tương ứng nằm ở hàng 2 của Bảng quy đổi tiền thưởng.
Lưu ý: Cố định bảng giá trị dò (Bảng quy đổi tiền thưởng) thành ($C$10:$E$11) để sao chép công thức xuống các giá trị ở phía dưới.
Sao chép công thức xuống các ô phía dưới ta được kết quả:
Kết quả nhận được
Trong trường hợp này, tại ô E6 ta nhận kết quả #NA bởi vì chức vụ “NV” không nằm trong Bảng quy định tiền thưởng và đây là loại tìm kiếm với giá trị chính xác tuyệt đối nên những giá trị dò tìm không khớp chính xác sẽ trả về lỗi #NA.
Cách giải quyết khi gặp kết quả #NA
Bạn có thể sử dụng kết hợp với hàm IFERROR để trả về một giá trị khác nếu như giá trị cần dò không được quy định trong bảng giá trị dò.
Để nhận được một kết quả khác, ta thay đổi công thức thành:
=IFERROR(HLOOKUP(C3,$C$10:$E$11,2,0),“Không đạt”).
Sửa lỗi #NA trong Excel
Ý nghĩa: Thực hiện dò tìm bằng hàm HLOOKUP, nếu loại chức vụ cần dò tại ô C3 không có trong bảng quy định tiền thưởng thì trả về kết quả không đạt.
Kết hợp hàm HLOOKUP với hàm IF
Ví dụ 3: Lập công thức xác định tiền thưởng cho từng nhân viên. Biết rằng, mức phụ cấp dựa theo chức vụ và xếp loại được quy định theo Bảng quy định tiền thưởng sau:
Ví dụ cách dùng hàm HLOOKUP trong Excel
Tại ô E3 ta sử dụng hàm IF kết hợp hàm HLOOKUP như sau:
= HLOOKUP ( C3 , $ C $ 10: $ E $ 12 , IF ( D3 = “A” , 2 , 3), 0) .
Nhập công thức
Ý nghĩa: Lúc này hàm HLOOKUP sẽ dò tìm loại chức vụ tại ô C3 trong Bảng quy định thưởng. Số thứ tự hàng cần lấy được chia ra 2 trường hợp sau:
- Kết hợp với hàm IF, nếu xếp loại ở ô D3 là loại A thì trả về kết quả tiền thưởng tương ứng với dòng 2 trong bảng.
- Nếu xếp loại ở ô D3 là B thì trả về kết quả tiền thưởng tương ứng với dòng 3 trong bảng.
Sao chép công thức xuống dưới ta được kết quả hoàn chỉnh:
Kết quả hoàn chỉnh
Lưu ý khi sử dụng hàm HLOOKUP
Khi sử dụng hàm HLOOKUP hãy lưu ý những điều sau để tránh xảy ra sai sót nhé:
- Giống như các hàm khác trong Excel, hàm HLOOKUP không phân biệt chữ hoa và chữ thường.
- Giá trị cần dò phải nằm trên cùng của bảng giá trị dò. Nếu bạn muốn dò tìm ở những vị trí khác hãy sử dụng hàm INDEX hoặc hàm MATCH nhé.
- Sử dụng đúng toán tử liên kết các thuộc tính trong Excel bằng dấu phẩy (,) hoặc dấu chấm phẩy (;) tùy theo cài đặt của mỗi máy tính.
- Nếu [Range_lookup] (phạm vi tìm kiếm) là TRUE (1) hoặc bỏ trống, các giá trị hàng đầu của bảng giá trị dò phải được sắp xếp theo thứ tự tăng dần (hoặc từ A-Z) từ trái sang phải.
Ngoài những ví dụ trên bạn hoàn toàn có thể linh hoạt kết hợp hàm HLOOKUP cùng với những hàm khác trong Excel sao cho phù hợp với yêu cầu công việc của mình. Nếu có bất kỳ thắc mắc gì hãy để lại bình luận phía dưới Azgad sẽ giải đáp cho bạn!
>>> Xem thêm: Cách dùng hàm SUMPRODUCT tính tổng tích dữ liệu trong Excel Khóa học tin học văn phòng từ cơ bản đến nâng cao dành cho người mới
