Cách sử dụng hàm HLOOKUP trong Excel đơn giản, có ví dụ chi tiết

Hàm HLOOKUP được sử dụng để dò tìm và trả kết quả nhất định theo yêu cầu. Hàm được dùng phổ biến khi giải các bài toán từ đơn giản đến phức tạp trong Excel. Tùy theo tình huống và nhu cầu của người dùng mà hàm được kết hợp với nhiều hàm khác. Bài viết dưới đây sẽ giúp bạn hiểu hơn về cách dùng hàm tính này một cách chi tiết thông qua các ví dụ cụ thể.

HLOOKUP là một hàm dùng để dò tìm dữ liệu trong một bảng (table), trên phạm vi hàng ngang và kết quả trả về tương ứng theo hàng dọc (cột). HLOOKUP được dùng trong các bảng tính nhân viên (xếp loại, tìm tên), sản phẩm (tìm tên, số lượng, đơn giá) dựa trên mã sản phẩm, mã vạch và tiêu chí xếp hạng.

Hàm công thức HLOOKUP trong Excel được sử dụng khá phổ biến trong trường học, văn phòng, bệnh viện,... Ví dụ như xếp loại học sinh dựa trên bảng điểm, dựa vào từng vị trí nhân viên đã được thiết lập sẵn để đưa ra mức lương phù hợp.

Hàm HLOOKUP trong Excel sử dụng để tìm kiếm giá trị tương ứng trên các ô thuộc hàng đầu tiên của bảng cần dò tìm.

Cú pháp của hàm = HLOOKUP(Lookup_value, Table_array, Row_index_ num, Range_lookup). Trong đó bao gồm các chỉ số với ý nghĩa riêng biệt:

• Lookup_value: Đây là giá trị cần dò tìm có thể là một ô tham chiếu, văn bản.

• Table_array: Là phạm vi dò tìm của hàm, thông thường là một bảng.

• Row_index_ num: Là số thứ tự của hàng trong bảng, giá trị muốn lấy chính là giá trị của hàng.

• Range_lookup: Loại kết quả được trả về, có thể là dò tìm tương đối (Range_lookup = 1) hoặc dò tìm chính xác (Range_lookup = 0).

Bạn có thể tham khảo các ví dụ bên dưới để hiểu về cách sử dụng HLOOKUP trong các trường hợp cụ thể.

Bài toán: Cho bảng dữ liệu (bảng 2) bao gồm mức điểm trung bình tương ứng với xếp loại. Yêu cầu đưa ra là trả về kết quả xếp loại cho từng học sinh ở bảng 1. 

Bảng dữ liệu (bảng 2) gồm mức điểm trung bình ứng với xếp loại, yêu cầu  trả kết quả xếp loại ở bảng 1 cho từng học sinh.

Trong trường hợp này, để dò tìm dữ liệu trong bảng 2 và trả về kết quả trong bảng 1 thì bạn có thể dùng hàm tính HLOOKUP.

• Bước 1: nhập hàm =HLOOKUP(C4,$B$11:$F$12,2,1) tại ô D4.

• Bước 2: Lúc này, hàm sẽ dò tìm giá trị gần bằng với giá trị ô C4 ở bảng 2. Nếu  tìm thấy giá trị đúng, hàm sẽ lấy xếp loại tương ứng trong hàng 2 (bảng 2) và trả kết quả về tại ô D4.

• Bước 3: Sau đó, bạn nhấp chuột vào ô D4 và kéo xuống để áp dụng công thức chung cho những ô còn loại trong cột D của bảng 1.

Bạn nhập hàm ở ô D4 sau đó hàm dò tìm giá trị lấy xếp loại tương ứng trong bảng 2 và trả về kết quả.

Bài toán: Một bảng dữ liệu (bảng 2) về mức phụ cấp cho nhân viên được chia theo từng chức vụ. Yêu cầu đặt ra là trả về kết quả phụ cấp đối với từng nhân viên tương ứng trong bảng 1.

Ví dụ bảng dữ liệu (bảng 2) mức phụ cấp cho nhân viên theo chức vụ, yêu cầu tính phụ cấp trong bảng 1 tương ứng với từng nhân viên.

Cách sử dụng hàm HLOOKUP để thực hiện yêu cầu của bài toán rất đơn giản giúp bạn dò tìm dữ liệu trong bảng 2 và trả kết quả trong bảng 1. 

• Bước 1: Ở vị trí ô D4, người dùng nhập hàm =HLOOKUP(C4,$B$11:$F$12,2,0).

• Bước 2: Khi đó, hàm tính HLOOKUP dò tìm giá trị ô C4 (“Nhân viên”) trong bảng 2. Hàm lấy giá trị tiền phụ cấp tương ứng trong hàng 2 (bảng 2) và trả kết quả tại ô D4 khi tìm thấy giá trị đúng.

• Bước 3: Cuối cùng, bạn nhấp vào D4 và kéo thả xuống để áp dụng công thức cho các ô còn lại trong cột D.

Sau khi nhập hàm tại ô D4, hàm dò tìm giá trị ô C4 trong bảng 2 và trả kết quả chính xác.

Một số trường hợp đặc biệt, người dùng có thể kết hợp hàm HLOOKUP trong Excel với IF. Theo ví dụ minh họa dưới đây, nếu nhân viên thuộc nhóm A, B, C và số ngày làm việc/tháng lớn hơn bằng 20, 24, 26 (ngày) thì “Đạt”. Nhân viên được đánh giá “không đạt” trong những trường hợp khác.

Đánh giá hiệu suất đạt và không đạt của nhân viên theo dữ liệu bảng tính.

• Bước 1: Tại ô E4, bạn nhập hàm =IF(D4=HLOOKUP(C4,$B$14:$F$15,2,0),'Đạt','Không Đạt').

• Bước 2: Hàm tính HLOOKUP thực hiện việc dò tìm giá trị  ô C4 (nhóm A) trong bảng 2 và trả về kết quả số chỉ tiêu ngày làm việc tương ứng. Sau đó, hàm IF sẽ so sánh kết quả này và số ngày làm việc thực tế của nhân viên tại ô D4 (20). Kết quả trả về đạt nếu số ngày làm việc thực tế bằng số ngày làm việc chỉ tiêu và ngược lại là không đạt.

Nhập hàm vào ô E4, hàm tính dò giá trị ô C4 trong bảng 2 và trả kết quả làm việc tương ứng của công nhân.

Người dùng cần lưu ý một số điều quan trọng sau khi sử dụng hàm tính HLOOKUP trong bảng Excel:

• Hàm chỉ có khả năng tìm kiếm trong hàng trên cùng của bảng dữ liệu.

• HLOOKUP không phân biệt giữa chữ thường hay chữ hoa.

• Trường hợp không xác định rõ hoặc để range_lookup ở trạng thái mặc định (TRUE) thì  phải biến hàng tìm kiếm thành một dãy được xếp trật tự từ A đến Z đảm bảo tra cứu chính xác.

• Khi tìm kiếm ngày tháng hoặc giá trị số cần đảm bảo hàng đầu tiên trong dải ô không  xếp theo giá trị văn bản. Ví dụ các số được xếp chính xác cần hiển thị dưới dạng (1, 2, 10, 100) thay vì (1, 10, 100, 2) xếp dưới dạng chuỗi. Các giá trị trả về không chính xác nếu sử dụng loại sắp xếp không chính xác.

• Khóa tìm kiếm dựa trên mẫu ký tự đại diện KHÔNG và biểu thức chính quy được hỗ trợ. 

• HLOOKUP đảm bảo hiệu suất tốt hơn nếu dải ô được sắp xếp đặt thành TRUE. 

Việc nắm bắt những lưu ý khi sử dụng hàm tính Lookup giúp người dùng tránh được sai sót và đảm bảo kết quả chuẩn xác.

Hàm HLOOKUP trong Excel không chạy được là do những sự cố không mong muốn sau đây:

• Người dùng sẽ nhận được thông báo lỗi N/A nếu HLOOKUP buộc phải nhìn lên trên, vượt khỏi hàng đầu tiên. Vì thế, bạn cần đảm bảo giá trị mà mình cần tìm phải nằm ngay hàng đầu tiên của bảng tra cứu.

• Nếu những số muốn thực hiện tra cứu đang hiển thị dưới dạng văn bản thì hàm tính sẽ phản hồi bằng lỗi.

• HLOOKUP có giới hạn chỉ xử lý tốt các giá trị tra cứu ít hơn 255 ký tự, nếu vượt quá sẽ gặp lỗi #VALUE!.

• Khi bạn muốn giữ nguyên mảng dữ liệu trong các công thức sao chép thì hãy sử dụng dấu $ để thiết lập tham chiếu.

• Nếu muốn thực hiện tham chiếu với bảng tính ở file hoặc sheets khác cần chỉ định đường dẫn chính xác và đầy đủ để tránh tình trạng HLOOKUP không chạy được.

• Trường hợp xóa hàng hay chèn trong bảng dữ liệu khiến vị trí hàng tra cứu thay đổi dẫn đến việc HLOOKUP ngừng hoạt động. Để khắc phục, bạn cần cập nhật số chỉ mục hàng phù hợp, không có khoảng trắng nào là được.

Trên đây là hướng dẫn cách sử dụng hàm HLOOKUP trong Excel đơn giản, có ví dụ chi tiết kèm theo. Mong rằng bài viết sẽ giúp bạn nắm bắt và áp dụng hàm công thức này để xử lý dữ liệu hiệu quả hơn trong bảng tính Excel.