Hàm HLOOKUP trong Excel: Dò tìm dữ liệu siêu tốc theo chiều ngang

Hàm HLOOKUP là một trong những hàm tra cứu hữu ích trong Excel, cho phép bạn tìm kiếm một giá trị trong hàng đầu tiên của bảng và trả về giá trị tương ứng từ một hàng khác trong cùng cột. Nói cách khác, HLOOKUP tìm kiếm theo chiều ngang, trong khi hàm VLOOKUP tìm kiếm theo chiều dọc. Cùng AntBook đi tìm hiểu từ A – Z với hàm đặc biệt này nhé!

1. Định nghĩa hàm HLOOKUP trong Excel là gì?

Hàm HLOOKUP trong Excel được sử dụng để tìm kiếm một giá trị trong hàng đầu tiên của một bảng dữ liệu theo chiều ngang, sau đó trả về giá trị từ một hàng khác trong cùng cột. Hàm này hữu ích khi cần tra cứu thông tin trong các bảng có cấu trúc theo hàng, chẳng hạn như danh sách nhân viên để tìm tên hoặc xếp loại, hoặc bảng sản phẩm để tra cứu đơn giá, số lượng dựa trên mã sản phẩm hay mã vạch.

Khi sử dụng, cần đảm bảo hàng đầu tiên của bảng chứa giá trị tra cứu và chỉ số hàng cần lấy dữ liệu phải nằm trong phạm vi bảng.

Hàm HLOOKUP là hàm dò tìm dữ liệu trong một bảng trên phạm vi hàng ngang

2. Công thức của hàm HLOOKUP trong Excel

Công thức hàm HLOOKUP

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Trong đó:

• lookup_value: Giá trị cần tìm trong hàng đầu tiên của bảng.
• table_array: Bảng hoặc mảng chứa dữ liệu cần tra cứu.
• row_index_num: Số thứ tự của hàng trong bảng từ đó trả về giá trị.
• range_lookup: Tùy chọn; xác định kiểu tìm kiếm:
  • TRUE hoặc bỏ qua: Tìm kiếm tương đối (gần đúng).
  • FALSE: Tìm kiếm chính xác.

Cú pháp: = HLOOKUP(Lookup_value, Table_array, Row_index_ num, Range_lookup)

Ví dụ: Giả sử bạn có bảng dữ liệu về sản phẩm với hàng đầu tiên là các mã sản phẩm và hàng thứ hai là giá tương ứng. Để tìm giá của sản phẩm có mã “SP001”, bạn có thể sử dụng công thức:

=HLOOKUP(“SP001”, A1:D2, 2, FALSE)

Công thức này sẽ tìm “SP001” trong hàng đầu tiên của phạm vi A1:D2 và trả về giá trị từ hàng thứ hai trong cùng cột.

Lưu ý:

• Nếu range_lookup là TRUE hoặc bị bỏ qua, hàng đầu tiên của table_array cần được sắp xếp theo thứ tự tăng dần để đảm bảo kết quả chính xác.
• Hàm HLOOKUP không phân biệt chữ hoa và chữ thường trong lookup_value.

3. Cách sử dụng hàm HLOOKUP trong Excel qua ví dụ thực tế

Cùng AntBook tìm hiểu cách sử dụng với từng vị dụ cụ thể dưới đây nhé!

3.1 Xếp loại danh hiệu học sinh với hàm HLOOKUP trong Excel 

Giả sử ta có hai bảng dữ liệu:

• Bảng 1: Danh sách học sinh với cột điểm trung bình cần tra cứu.
• Bảng 2: Bảng xếp loại gồm các mức điểm trung bình và danh hiệu tương ứng.

Sử dụng hàm HLOOKUP để dò tìm dữ liệu trong bảng 2 và trả về kết quả trong bảng 1

Để xác định danh hiệu của từng học sinh dựa trên điểm trung bình, ta sử dụng hàm HLOOKUP theo công thức sau:

=HLOOKUP(C4, $B$11:$F$12, 2, 1)

Trong đó:

• C4: Điểm trung bình của học sinh tại ô cần tra cứu.
• $B$11:$F$12: Vùng dữ liệu chứa mức điểm và danh hiệu (Bảng 2).
• 2: Lấy dữ liệu từ hàng thứ 2 trong bảng tra cứu.
• 1: Tìm kiếm tương đối, tức là tìm giá trị gần nhất mà không vượt quá giá trị tra cứu.

Sau khi nhập công thức tại ô D4, bạn có thể kéo xuống để áp dụng công thức cho toàn bộ danh sách học sinh. Hàm sẽ tự động tra cứu và trả về danh hiệu phù hợp dựa trên điểm trung bình của từng học sinh.

Kết quả sau khi đã sắp xếp loại danh hiệu cho từng học sinh

3.2 Ứng dụng hàm HLOOKUP trong Excel để tính phụ cấp nhân viên

Giả sử ta có hai bảng dữ liệu:

• Bảng 1: Danh sách nhân viên với chức vụ cần tra cứu.
• Bảng 2: Mức phụ cấp tương ứng cho từng chức vụ.

Sử dụng hàm HLOOKUP để dò tìm dữ liệu trong bảng 2 và trả về kết quả trong bảng 1

Để xác định phụ cấp của từng nhân viên theo chức vụ, ta sử dụng hàm HLOOKUP với công thức sau:

=HLOOKUP(C4, $B$11:$F$12, 2, 0)

Trong đó:

• C4: Chức vụ của nhân viên cần tra cứu.
• $B$11:$F$12: Phạm vi chứa thông tin chức vụ và mức phụ cấp (Bảng 2).
• 2: Lấy dữ liệu từ hàng thứ 2 trong bảng tra cứu (mức phụ cấp).
• 0: Tìm kiếm chính xác, chỉ trả về kết quả nếu chức vụ khớp hoàn toàn.

Sau khi nhập công thức vào D4, bạn có thể kéo xuống để áp dụng công thức cho các nhân viên còn lại trong danh sách. Hàm sẽ tự động tra cứu và trả về mức phụ cấp phù hợp theo chức vụ của từng người.

Chỉ cần nhấp vào ô D4 và kéo xuống để áp dụng công thức chung là ra kết quả

3.3 Cách dùng hàm HLOOKUP Excel kết hợp cùng hàm IF đánh giá nhân viên

Trong tình huống này, ta cần kiểm tra xem nhân viên có đáp ứng đủ số ngày làm việc theo yêu cầu của nhóm mà họ thuộc hay không.

Hàm HLOOKUP để dò tìm dữ liệu trong bảng 2 và trả về kết quả trong bảng 1

Bảng dữ liệu:

• Bảng 1: Danh sách nhân viên gồm nhóm, số ngày làm việc thực tế.
• Bảng 2: Quy định số ngày làm việc tối thiểu cho từng nhóm A, B, C.

Công thức tại ô E4: =IF(D4 >= HLOOKUP(C4, $B$14:$F$15, 2, 0), “Đạt”, “Không Đạt”)

Trong đó:

• HLOOKUP(C4, $B$14:$F$15, 2, 0): Tìm nhóm của nhân viên tại ô C4 trong Bảng 2 và trả về số ngày làm việc tối thiểu yêu cầu.
• IF(D4 >= … , “Đạt”, “Không Đạt”): So sánh số ngày làm thực tế ở D4 với mức chỉ tiêu. Nếu số ngày làm thực tế lớn hơn hoặc bằng chỉ tiêu, kết quả là “Đạt”, ngược lại trả về “Không Đạt”.

Cách áp dụng: Sau khi nhập công thức vào E4, kéo thả xuống để áp dụng cho toàn bộ danh sách nhân viên.

Để áp dụng công thức cho các ô còn lại trong cột D, chỉ cần nhấp vào D4 và kéo thả xuống

5. So sánh hàm HLOOKUP và hàm VLOOKUP trong Excel

Khi bảng dữ liệu của bạn có nhiều cột hơn hàng, việc sử dụng HLOOKUP sẽ hiệu quả hơn VLOOKUP. Còn khi cần lấy dữ liệu từ nhiều hàng dựa trên một giá trị tra cứu, HLOOKUP là lựa chọn phù hợp.Sau đây là bảng so sánh chi tiết giữa 2 hàm:

6. Mẹo và thủ thuật sử dụng hàm HLOOKUP hiệu quả

– Sử dụng tham chiếu tuyệt đối ($) để cố định bảng dữ liệu khi sao chép công thức. Việc sử dụng tham chiếu tuyệt đối sẽ ngăn table_array bị thay đổi khi bạn sao chép công thức sang các ô khác. Ví dụ, thay vì A1:D4, bạn nên sử dụng $A$1:$D$4.

– Sắp xếp dữ liệu trong hàng đầu tiên của bảng theo thứ tự tăng dần nếu bạn sử dụng range_lookup là TRUE. Điều này giúp hàm HLOOKUP tìm kiếm chính xác và nhanh chóng hơn.

– Kết hợp hàm HLOOKUP với các hàm khác như IF, SUM, AVERAGE để tạo ra các điều kiện phức tạp hơn. Ví dụ, bạn có thể kết hợp HLOOKUP với hàm IF để kiểm tra điều kiện trước khi trả về kết quả.

– Sử dụng kỹ thuật đặt tên cho bảng dữ liệu để dễ dàng tham chiếu trong công thức. Thay vì sử dụng tham chiếu ô, bạn có thể đặt tên cho bảng dữ liệu và sử dụng tên đó trong công thức.

– Sử dụng ký tự đại diện (? và *) trong lookup_value. 1 Dấu chấm hỏi (?) đại diện cho một ký tự bất kỳ, còn dấu sao (*) đại diện cho một chuỗi ký tự bất kỳ. Ví dụ, HLOOKUP(“SP00?”, A1:D4, 3, FALSE) sẽ tìm kiếm giá trị bắt đầu bằng “SP00” và theo sau là một ký tự bất kỳ.

Hướng dẫn mẹo sử dụng HLOOKUP hiệu quả

7. Tổng hợp các lỗi thường gặp khi sử dụng hàm HLOOKUP Excel

Khi sử dụng hàm HLOOKUP, bạn có thể gặp một số lỗi sau:  

• #N/A: Xuất hiện khi giá trị cần tra cứu không có trong hàng đầu tiên của bảng. Nguyên nhân có thể do dữ liệu không tồn tại, nhập sai chính tả hoặc không đúng kiểu dữ liệu.
• #REF!: Xảy ra khi chỉ số hàng (row_index_num) nhỏ hơn 1 hoặc vượt quá số lượng hàng của bảng.
• #VALUE!: Phát sinh nếu vùng dữ liệu tra cứu (table_array) không hợp lệ.

Lỗi thường gặp trong hàm HLOOKUP trong Excel

Hàm HLOOKUP trong Excel là một công cụ giúp bạn dễ dàng tra cứu dữ liệu theo hàng, tối ưu hóa quy trình làm việc và nâng cao hiệu suất xử lý bảng tính. Hy vọng, bài viết mà AntBook gửi đến đã giúp bạn có thêm nhiều thông tin hữu ích dành cho mình.

Nếu bạn muốn nâng cao kỹ năng Excel và PowerPoint, bộ Sách Word – Excel – Powerpoint – Thủ Thuật Tin Học Văn Phòng làm chủ tin học văn phòng một cách bài bản và chuyên sâu, sẽ là lựa chọn lý tưởng. Bộ sách cung cấp kiến thức từ cơ bản đến nâng cao, kèm theo hướng dẫn thực hành chi tiết, giúp bạn tự tin làm chủ các công cụ văn phòng quan trọng này.