Cách sử dụng hàm Hlookup trong excel và cú pháp cụ thể
Hlookup là hàm gì?
Hàm hlookup chính là một trong những hàm cơ bản trong excel, có chức năng chính là tìm kiếm giá trị trong hàng của bảng tham chiếu hoặc mảng giá trị có cú pháp lệnh đơn giản áp dụng khi các giá trị nằm trên hàng ngang. Nếu các giá trị so sánh nằm trên cột thì cần áp dụng hàm vlookup.
Ký tự H trong hlookup chính là từ viết tắt của “Horizontal” nghĩa là “Ngang”.
Đây là hàm được sử dụng nhiều trong excel giúp bạn tham chiếu giá trị trên các dòng để trả về kết quả chính xác thỏa mãn điều kiện. Chính bởi sự thông dụng này nên bạn cần nắm rõ kiến thức để có thể sử dụng nó một cách thành thạo đem lại hiệu quả trong công việc.
Cách dùng hàm hlookup trong Excel
Cú pháp hàm
| HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup) |
Trong đó:
Lookup_value (giá trị đem dò): Giá trị tuyệt đối của bảng tham chiếu đầu tiên cần tìm giá trị.
Table_ array (bảng giá trị dò): Phải để ở dạng địa chỉ tuyệt đối (bằng cách chọn bảng và nhấn F4), khi chọn không quét tiêu đề.
Row_index_Num (số thứ tự hàng cần lấy): Số thứ tự từ trên xuống dưới bắt đầu từ số 1 cần lấy giá trị trong bảng tham chiếu.
Range_lookup (phạm vi tìm kiếm): Nếu là 1 (True) là dò tìm tương đối, 0 (False) là thực hiện dò tìm chính xác.
Chú ý:
- Nếu chọn dò tìm tương đối, một kết quả gần sát lookup value nhất sẽ được trả về trong trường hợp không tìm được giá trị chính xác.
- Nếu chọn dò tìm chính xác, trong trường hợp không có giá trị chính xác, kết quả trả về #N/A.
- Hàm HLOOKUP cho phép sử dụng các kí tự đại diện sau cho tham số lookup_value:
- Dấu sao (*) để khớp với một chuỗi các kí tự
- Dấu hỏi (?) để khớp với bất kì một kí tự đơn nào
- Khi sử dụng công thức, để cố định bảng Table_array khi copy công thức cần ấn dấu “$”
Ví dụ áp dụng
Xếp loại bằng cấp
Bước 1: Để xếp loại bằng cấp ở bảng 1 cần dựa vào dữ liệu ở bảng 2
Tại ô D4 sử dụng công thức hlookup:
=HLOOKUP(C4,$B$12:$F$13,2,1)
Trong đó:
- C4: Điểm TB đối chiếu trong dòng đầu tiên của bảng tham chiếu.
- $B$12:$F$13: Địa chỉ tuyệt đối trong bảng tham chiếu (Bảng 2)
- 2: Dòng thứ 2 trong đơn vị của bảng tham chiếu.
- 1: Giá trị đã được sắp xếp theo chiều tăng dần (ngầm định)
Và ấn Enter và trả về kết quả
Bước 2: Tại góc phải của ô vừa ra kết quả, xuất hiện dấu cộng màu đen, ấn đúp để tự động điền các giá trị còn lại là hoàn tất
Tìm kiếm giá trị mã đơn vị
Nhập công thức lệnh: =HLOOKUP(A2:A4;$A$6:$D$7;2;1)
Trong đó:
- A2:A4: Địa chỉ mã đơn vị đối chiếu trong dòng đầu tiên của bảng tham chiếu.
- $A$6:$D$7: Địa chỉ tuyệt đối trong bảng tham chiếu.
- 2: Dòng thứ 2 trong đơn vị của bảng tham chiếu.
- 1: Giá trị đã được sắp xếp theo chiều tăng dần (ngầm định)
► XEM THÊM: Hàm if trong excel: Cách sử dụng đơn giản [Hướng dẫn bài tập]
Hàm HLOOKUP kết hợp hàm IF
Ví dụ về hàm hlookup nâng cao: Nếu nhân viên ngân hàng thuộc nhóm D, E, F, G có doanh số lớn hơn 22, 24, 25, 28 sẽ được đánh giá là “Đạt” hoặc “Không đạt”
Bước 1: Tại ô F4 sử dụng Hàm HLOOKUP kết hợp với hàm IF như sau:
=IF(D4>=HLOOKUP(C4,$B$13:$F$14,2,0),”Đạt”,”Không Đạt”)
Kết quả trả lại:
Lưu ý khi áp dụng hàm hlookup tìm kiếm giá trị
- Hàm hlookup chỉ thực hiện ở hàng trên cùng của table_array. Nếu muốn tìm những vị trí khác cần sử dụng hàm index hoặc hàm match.
- Nếu range_lookup đặt là “TRUE” hoặc bỏ trống thì các giá trị ở hàng đầu tiên của table_array được sắp xếp theo tứ tự tăng dần từ trái qua phải.
- Hàm hlookup không phân biệt chữ hoa hay thường.
Hàm hlookup sử dụng cho Microsoft Excel các phiên bản 2016, 2013, 2010, 2007 và các phiên bản cũ hơn.
Phân biệt hàm hlookup và hàm vlookup
Khi đã nắm rõ được khái niệm về hàm vlookup và hlookup, các bạn sẽ nhận ra điểm khác nhau cơ bản nhất giữa 2 hàm này về cơ chế hoạt động. Để nhận định một cách chính xác thì các bạn tham khảo ví dụ minh họa dưới đây.
Ví dụ về 2 hàm VLOOKUP và HLOOKUP có:
- Chữ A chính là Lookup_value
- Bảng có 5 cột, 5 dòng là Table_array
- 4 là col_index_num (hàm vlookup) và row_index_num (hàm hlookup)
- 0 là range_lookup
Hoặc có thể nhìn thấy điểm khác nhau rõ rệt như:
| Cơ chế hoạt động | VLOOKUP | HLOOKUP |
| Lookup_value (Giá trị tìm kiếm) | Tìm trong cột đầu tiên của Table_array | Tìm trong dòng đầu tiên của Table_array |
| Kết quả cần tìm | Nằm ở cột thứ mấy tính từ vị trí giá trị tìm kiếm so sánh sang phải | Nằm ở dòng thứ mấy tính từ vị trí giá trị tìm kiếm so sánh xuống dưới |
| Từ khóa trong hàm | V = Vertical = Chiều dọc kết quả tại cột thứ mấy | H = Horizontal = Chiều ngang kết quả tại dòng thứ mấy |
Cơ chế áp dụng của hàm hlookup và hàm vlookup sẽ được căn cứ vào Lookup_value và vùng bảng Table_Array chứa dữ liệu cần tìm kiếm.
- Khi Lookup_value nằm trên cột đầu tiên thì áp dụng hàm vlookup
- Khi Lookup_value nằm trên dòng đầu tiên thì áp dụng hàm Hlookup
Bạn có thể kết hợp 2 hàm vlookup và hàm hlookup một cách dễ dàng nhưng tùy vào từng trường hợp ta sẽ kết hợp. Cụ thể ta có thể xem tại công thức ô E3 như sau:
Nhập công thức lệnh: =D3*VLOOKUP(HLOOKUP(B3,$C$9:$F$10,2,0),$B$14:$D$17,3,0)
Công thức này có thể hiểu đơn giản như giá trị trả về của hàm hlookup là giá trị tìm kiếm của hàm vlookup.
Vào Tab Formulas thuộc nhóm Formula Auditing tìm Evaluate Formula hộp thoại hiện ra ấn vào Evaluate để xem cách tính toán công thức từ đó bạn sẽ thấy được rõ hơn.
► XEM THÊM NGAY: Cách sử dụng các hàm tìm kiếm trong Excel [HƯỚNG DẪN]
Hi vọng với chia sẻ chi tiết trên đây các bạn có thể hiểu rõ hơn về hàm hlookup trong excel một cách rõ ràng và chính xác nhất để áp dụng vào trong công việc và quá trình học tập hiệu quả. Đây là một trong những kỹ năng cơ bản của dân văn phòng nên các bạn đừng bỏ qua nhé. Chúc các bạn thành công!
