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.

Bảng ngang gồm hàng Mã, Tên, Doanh số và bảng nhỏ Tìm, Kết quả
Bảng tra cứu nằm trong vùng A1:F3, và cột H chứa các giá trị cần tìm.

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.

Công thức HLOOKUP với HN* trả về doanh số 90
Ký tự đại diện chỉ hoạt động khi tham số cuối là FALSE.

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.

Công thức HLOOKUP tìm hn-02 nhưng trả về 90 của mã HN-02
Hàm coi HN-02 và hn-02 là một, nên lấy mã đứng trước.

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.

Công thức INDEX, MATCH và EXACT trả về đúng 150 của mã hn-02
Hàm EXACT so sánh từng mã và có phân biệt chữ hoa, chữ thường.

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.

Công thức INDEX và MATCH tìm theo tên ở hàng 2 và trả về mã hn-02
Hàm MATCH tìm vị trí ở hàng 2, rồi hàm INDEX lấy mã ở cùng vị trí trong hàng 1.

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".

Công thức IFERROR hiện thông báo khi không tìm thấy mã XX-99
Thông báo rõ ràng giúp người đọc báo cáo không hiểu nhầm là lỗi công thức.

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")

Công thức XLOOKUP hiện thông báo mà không cần IFERROR
Hàm XLOOKUP chỉ hiện thông báo khi không tìm thấy, không che các lỗi khác.

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.

Công thức HLOOKUP lấy đơn giá 350 từ sheet DuLieu
Tên sheet và dấu chấm than đứng trước vùng dữ liệu.

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.