Hàm HLookup cách dùng và bài tập áp dụng Hlookup

Hàm Hlookup sử dụng như thế nào, kết hợp hàm Hlookup và Left, Hlookup với Right, Hlookup với if sẽ đem lại hiệu quả như thế nào? chúng ta cùng tìm hiểu qua bài viết dưới đây nhé. Trong bài này sử dụng bản excel 2010, các phiên bản excel khác cách sử dụng Hlookup cũng tương tự.

1. Hàm Hlookup

- Công dụng: Sử dụng hàm hlookup, bạn sẽ dễ dàng tìm kiếm và tham chiếu theo các dòng, các cột để sau khi thỏa mãn các điều kiện đặt ra kết quả chính xác được trả về một cách nhanh chóng.

- Cú pháp lệnh (syntax): HLOOKUP(Lookup_value, Table_array, row_index_ num, Range_lookup)

Trong đó :

– Lookup_value: Giá trị cần dò tìm.

– Table_array: Bảng chứa dữ liệu cần dò tìm, bạn nhấn F4 để khoá địa chỉ tuyệt đối cho mục đích copy công thức tự động.

_ Nếu range_lookup là TRUE (tức ĐÚNG) thì các giá trị trong hàng thứ nhất của table_array phải được đặt theo thứ tự tăng dần: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE; nếu không, hàm HLOOKUP có thể đưa ra giá trị không đúng.

_ Nếu range_lookup là FALSE (tức SAI), thì không cần phải sắp xếp table_array..

– Row_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm.

_ Nếu row_index_num nhỏ hơn 1, hàm HLOOKUP trả về giá trị lỗi #VALUE!

_ Nếu row_index_num lớn hơn số hàng trên table_array, hàm HLOOKUP trả về giá trị lỗi #REF!

– Range_lookup: Kiểu dò tìm (Là giá trị Logic: TRUE=1, FALSE=0 quyết định dò tìm chính xác hay tương đổi với bảng dò).

+ Nếu Range_lookup = 0: dò tìm chính xác.

_ Nếu không tìm thấy kết quả khớp chính xác, hàm sẽ trả về giá trị lỗi #N/A.

+ Nếu Range_lookup = 1: dò tìm tương đối.

+ Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1

Lưu ý: Hlookup được sử dụng khi bảng dữ liệu dò có chứa giá trị dò tìm sắp xếp theo dòng hay hàng (theo chiều ngang) nếu bảng dữ liệu có giá trị dò tìm sắp xếp theo chiều dọc (theo cột) chúng ta phải sử dụng hàm Vlookup.

* Ví dụ 1 bài tập sử dụng HLookup

- Bạn hãy điền vào cột Đơn giá theo Loại vật tư từ thông tin Bảng phụ theo ví dụ dưới đây:

Trong ví dụ trên, tại ô D5 ta gõ công thức: =HLOOKUP(B5,$F$6:$I$7,2,0)

Trong đó:

Hlookup: là hàm dùng để tìm kiếm ra LOẠI VẬT TƯ tại BẢNG PHỤ có Đơn giá.

B5: Là Giá trị cần dò tìm; ở đây là các Giá trị trong cột Loại Vật tư (Xi Măng, Bột màu, Sơn gỗ)

$F$6:$I$7: Bảng dữ liệu dò tìm, chính là F6:I7 nhưng khoá địa chỉ tuyệt đối (nhấn F4) để Copy công thức xuống các ô D6->D12.

2: Thứ tự dòng giá trị cần lấy, trong trường hợp này chính là dòng Đơn giá;

0: là kiểu dò tìm chính xác.

- Ở ví dụ tiếp theo chúng ta sẽ thực hiện dò tìm tương đối (kiểu dò trong hàm hlookup là:1) Xếp loại học lực dựa vào Điểm trung bình (ĐTB) trong Bảng Xếp Loại. Sau khi nhập công thức và copy xuống các ô từ D5->D10 ta được bảng sau:

Trong ví dụ này thì ở ô D4 nhập công thức: =HLOOKUP(C4,$F$6:$J$7,2,1)

Trong đó:

C4: là giá trị dò tìm

$F$6:$J$7: Là bảng dữ liệu cần dò giá trị

2: Thứ tự dòng giá trị cần lấy, trong trường hợp này chính là dòng Đơn giá;

1: Kiểu dò tìm tương đối (gần đúng)

2. Hàm Hlookup và Left

- Như các bạn đã biết, hàm Left là hàm lấy ký tự bên trái một chuỗi bất kỳ, khi kết hợp Hlookup và left giúp cho việc dò tìm kết quả nhanh và chính xác trong nhiều bài toán cụ thể.

* Cú pháp hàm left:=Left(text,n)

Ví dụ: Điền vào cột Tên hàng biết rằng ký tự đầu của Phiếu xuất kho là Mã VT

- Như vậy, trong ô C5 chúng ta nhập công thức: =HLOOKUP(LEFT(B5,1),$E$6:$H$7,2,0) sau đó copy (Fill) kết quả xuống là hoàn thành yêu cầu bài toán.

- Ý nghĩa công thức: Đầu tiên hàm Left lấy 1 ký tự bên trái ô B5, sau đó dò tìm ký tự này trong bảng E6:H7, khi dò tìm thấy giá trị này sẽ trả kết quả trong dòng thứ 2 của bảng E6:H7.

3. Hàm Hlookup và Right

- Hàm Right là hàm lấy ký tự bên phải một chuỗi bất kỳ, khi kết hợp Hlookup và Right cũng tương tự như Hlookup kết hợp left giúp cho việc dò tìm kết quả nhanh và chính xác trong nhiều bài toán cụ thể.

* Cú pháp hàm Right: =Right(text,n)

Ví dụ: Điền vào cột Tên hàng biết rằng ký tự cuối của Phiếu xuất kho là Mã VT

- Như vậy trong ô C5 chúng ta nhập công thức: =HLOOKUP(RIGHT(B5,1),$E$5:$H$6,2,0) sau đó Fill (copy) công thức xuống là chúng ta hoàn thành bài toán.

- Ý nghĩa công thức: Đầu tiên hàm Right sẽ lấy 1 ký tự bên phải ô B5, sau đó dò tìm ký tự này trong bảng E5:H6, khi gặp giá trị dò tìm sẽ lấy giá trị trả về ở dòng thứ 2 trong bảng dò tìm E5:H6.

4. Hàm Hlookup kết hợp IF

- Hàm IF là một trong những hàm điều kiện được dùng phổ biết nhất trong Excel, khi kết hợp IF và Vlookup giúp cho nhiều bài toán được giải quyết nhanh chóng.

* Cú pháp hàm IF:=if(logical_test, value if true, value if false)

Ví dụ: Điền vào cột mức Giá của Mã VT biết rằng, ký tự đầu phiếu xuất kho là Mã VT, ký tự cuối Phiếu xuất kho là Giá.

- Như vậy, trong ô C5 nhập công thức: =HLOOKUP(LEFT(B5,1),$E$5:$H$7,IF(RIGHT(B5,1)="1",2,3),0) sau đó Fill (copy) kết quả xuống là chúng ta hoàn thành yêu cầu bài toán.

+ Ý nghĩa công thức:

- Đầu tiên hàm Left lấy 1 ký tự bên trái ô B5, sau đó dò giá trị này trong bảng E5:H7, khi gặp giá trị dò tìm sẽ trả về giá trị trong dòng mà hàm IF trả về.

- Đối với hàm IF, đầu tiên hàm Right lấy 1 ký tự bên phải ô B5, kiểm tra xem giá trị này có ="1" hay không, nếu bằng thì trả về 2, nếu không thì trả về 3, giá trị IF trả về này quy định dòng trả về giá trị trong hàm Hlookup.

Mời các bạn xem video hướng dẫn chi tiết - How to use the HLookup and IF functions together in Excel

Hi vọng qua phần hướng dẫn cách sử dụng Hlookup sẽ giúp ích cho các bạn hiểu rõ hơn cách sử dụng hlookup để áp dụng cho bảng tính thực tế.

Ngoài Hlookup, còn có các hàm dò tìm rất hữu ích và phổ biến khác như Vlookup, Index và Match, . . . các bài hướng dẫn này đều được giới thiệu trên HayHocHoi.Vn, các bạn hãy tìm đọc và tham khảo nhé.