Khi đã biết tra cứu theo hàng ngang, bạn sẽ sớm gặp các tình huống khó hơn như mã không nhớ đủ hoặc mã chỉ khác nhau ở chữ hoa. Hàm HLOOKUP nâng cao trong Excel giúp bạn xử lý các tình huống này, và bạn còn biết lúc nào nên đổi sang hàm khác.
Bài viết này dành cho người đã biết cách dùng cơ bản. Nếu mới bắt đầu, bạn nên đọc bài hàm HLOOKUP trong Excel trước. Bài không kèm file vì bạn chỉ cần tự gõ bảng. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, và màu tiêu đề trong ảnh chỉ để dễ nhìn.
Bạn cần gõ bảng này trước khi làm theo
Bạn gõ bảng ngang dưới đây vào một sheet trống, bắt đầu từ ô A1. Bạn gõ đúng chữ hoa và chữ thường, vì mã HN-02 và hn-02 là 2 mã khác nhau.
Mã | SG-01 | HN-02 | hn-02 | DN-03 | HP-04 |
|---|---|---|---|---|---|
Tên | An | Bình | Cường | Dung | Giang |
Doanh số | 120 | 90 | 150 | 70 | 110 |
Sau đó, bạn gõ "Tìm" vào H1 và "Kết quả" vào I1. Các ô H2, H3, H4, H5 lần lượt chứa HN*, hn-02, Cường và XX-99.

Trước mỗi mẹo, bạn xóa kết quả cũ ở cột I. Nếu Excel báo lỗi khi bạn gõ dấu phẩy trong công thức, máy của bạn đang dùng dấu chấm phẩy. Khi đó, bạn đổi mọi dấu phẩy ngăn cách trong công thức thành dấu chấm phẩy.
Mẹo 1: Ký tự đại diện giúp tìm khi không nhớ đủ mã
Dấu * thay cho không hoặc nhiều ký tự bất kỳ, còn dấu ? thay cho đúng một ký tự. Bạn gõ công thức sau vào I2:
=HLOOKUP(H2,$B$1:$F$3,3,FALSE)
Ô H2 chứa HN*, nên hàm tìm mã bắt đầu bằng HN và trả về 90 của mã HN-02.

Bạn có thể đổi điều kiện tìm theo nhiều cách. Ví dụ, =HLOOKUP("HN-0?",B1:F3,3,FALSE) và =HLOOKUP("*02",B1:F3,3,FALSE) đều trả về 90. Hàm chỉ lấy kết quả đầu tiên khớp, nên bạn cần cẩn thận khi có nhiều mã giống nhau.
Mẹo 2: Hàm EXACT giúp phân biệt chữ hoa, chữ thường
Hàm HLOOKUP không phân biệt chữ hoa và chữ thường. Bạn gõ công thức sau vào I3 để tìm mã hn-02:
=HLOOKUP(H3,$B$1:$F$3,3,FALSE)
Ô I3 trả về 90, tức là doanh số của mã HN-02 đứng trước. Kết quả đúng của mã hn-02 phải là 150.

Bạn đổi sang công thức sau tại I3:
=INDEX($B$3:$F$3,MATCH(TRUE,EXACT($B$1:$F$1,H3),0))
Ô I3 trả về đúng 150.

Trên Excel 2019 trở về trước, công thức này có thể cần nhấn Ctrl + Shift + Enter. Tôi chỉ kiểm tra trên Excel 365.
Mẹo 3: Hàm INDEX và MATCH lấy được hàng nằm phía trên
Hàm HLOOKUP chỉ tìm ở hàng đầu tiên của vùng, và chỉ lấy được các hàng nằm dưới. Nếu tìm theo tên ở hàng 2 để lấy doanh số ở hàng 3, công thức =HLOOKUP(H4,$B$2:$F$3,2,FALSE) vẫn làm được và trả về 150. Nhưng nếu cần lấy mã ở hàng 1 phía trên, bạn gõ công thức sau vào I4:
=INDEX($B$1:$F$1,MATCH(H4,$B$2:$F$2,0))
Ô I4 trả về hn-02, là mã của Cường.

Mẹo 4: Hàm IFERROR xử lý khi không tìm thấy
Mã không tồn tại sẽ làm hàm báo #N/A. Bạn gõ công thức sau vào I5 để hiện thông báo dễ hiểu hơn:
=IFERROR(HLOOKUP(H5,$B$1:$F$3,3,FALSE),"Không có mã này")
Ô I5 trả về dòng chữ "Không có mã này".

Bạn nên nhớ rằng IFERROR che mọi loại lỗi, kể cả lỗi do bạn gõ sai công thức. Trên Excel 365, hàm XLOOKUP có sẵn tham số cho trường hợp này. Bạn có thể gõ công thức sau vào I5:
=XLOOKUP(H5,$B$1:$F$1,$B$3:$F$3,"Không có mã này")

Mẹo 5: Hàm HLOOKUP tra cứu dữ liệu ở sheet khác
Bạn tạo thêm một sheet tên DuLieu và gõ bảng ngang sau, bắt đầu từ ô A1:
Mã | SP1 | SP2 | SP3 |
|---|---|---|---|
Đơn giá | 150 | 350 | 420 |
Sau đó, bạn quay lại sheet chứa bảng ngang và gõ công thức sau vào I2. Công thức này tìm trực tiếp mã SP2, nên không liên quan đến ô H2 bên cạnh.
=HLOOKUP("SP2",DuLieu!$A$1:$D$2,2,FALSE)
Ô I2 trả về 350.

Nếu tên sheet có dấu cách, bạn đặt tên trong dấu nháy đơn, như 'Dữ liệu'!$A$1:$D$2.
Bạn thường gặp những lỗi nào với hàm HLOOKUP?
Hàm báo lỗi #REF!: Số hàng lớn hơn số hàng của vùng. Ví dụ,
=HLOOKUP("SG-01",B1:F3,5,FALSE)báo lỗi vì vùng chỉ có 3 hàng.Hàm báo lỗi #VALUE!: Số hàng nhỏ hơn 1. Ví dụ,
=HLOOKUP("SG-01",B1:F3,0,FALSE)báo lỗi.Hàm báo lỗi #N/A: Giá trị cần tìm không có ở hàng đầu tiên. Bạn cần kiểm tra khoảng trắng thừa trong mã.
Kết quả bị lệch khi kéo công thức: Vùng tra cứu chưa được cố định. Bạn cần thêm dấu đô la như
$B$1:$F$3.
Người đọc thường hỏi gì về hàm HLOOKUP nâng cao trong Excel?
Hàm XLOOKUP có dùng được ký tự đại diện không?
Hàm XLOOKUP dùng được nếu bạn đặt tham số chế độ khớp là 2. Ví dụ, =XLOOKUP("HN*",B1:F1,B3:F3,"",2) trả về 90.
Hàm XLOOKUP có phân biệt chữ hoa, chữ thường không?
Hàm XLOOKUP mặc định không phân biệt, nhưng bạn có thể kết hợp với EXACT. Công thức =XLOOKUP(TRUE,EXACT(B1:F1,"hn-02"),B3:F3) trả về 150.
Bạn muốn dùng Excel thành thạo cho công việc văn phòng?
Khi hiểu giới hạn của từng hàm tra cứu, bạn sẽ chọn đúng công cụ và tránh được các kết quả sai khó phát hiện.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.