Hàm LOOKUP trong Excel tìm một giá trị trong một dòng hoặc một cột, rồi trả về giá trị nằm ở vị trí tương ứng. Hàm này có hai ứng dụng rất hay dùng khi đi làm: lấy ô cuối cùng có dữ liệu và thay thế các IF lồng nhau.

Bài viết đi từ 3 ví dụ cơ bản tới 3 ứng dụng hay gặp khi đi làm. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh.

Mỗi ví dụ dùng một bảng nhỏ riêng, và ảnh hiện đủ bảng cùng công thức. Ảnh dùng định dạng số tiếng Anh, nên máy của bạn có thể hiện 12.000 thay vì 12,000 và 4,8 thay vì 4.8. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy.

Hàm LOOKUP có cú pháp thế nào?

Hàm LOOKUP có 2 dạng:

Dạng

Cú pháp

Dạng này làm gì

Vector

=LOOKUP(giá trị tìm, vùng tìm, vùng kết quả)

Hàm tìm trong một vùng và lấy kết quả ở một vùng khác.

Mảng

=LOOKUP(giá trị tìm, bảng)

Hàm tìm ở cột đầu của bảng và lấy kết quả ở cột cuối, khi bảng có số dòng không ít hơn số cột. Nếu bảng rộng hơn cao, hàm tìm ở dòng đầu và lấy ở dòng cuối.

Bạn cần nhớ 2 quy tắc của hàm LOOKUP trước khi làm ví dụ:

  • Vùng tìm phải xếp tăng dần. Nếu vùng không xếp tăng dần, hàm có thể trả về kết quả sai mà không báo lỗi, hoặc báo lỗi #N/A dù giá trị có trong vùng.

  • Hàm luôn tìm gần đúng. Khi không có giá trị khớp, hàm lấy giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tìm. Khi giá trị tìm nhỏ hơn mọi giá trị trong vùng, hàm báo lỗi #N/A. Vì vậy, mã không có trong bảng vẫn có thể ra kết quả. Ở ví dụ 1, mã SP99 không tồn tại nhưng hàm vẫn trả về đơn giá của SP05.

Ví dụ 1: Bạn tra cứu theo cột

Bảng ở ví dụ này có mã hàng đã xếp tăng dần, và bạn cần lấy đơn giá theo mã ở cột E.

Bạn chọn ô F2, gõ công thức sau rồi nhấn Enter:

=LOOKUP(E2,$A$2:$A$6,$C$2:$C$6)

Bạn đang gõ vào ô F2 công thức LOOKUP, vùng mã hàng và vùng đơn giá được tô viền màu
Hàm tìm mã SP03 trong cột A và lấy giá trị cùng dòng ở cột C

Dấu $ giữ nguyên hai vùng khi bạn kéo công thức. Bạn chọn lại ô F2, đưa chuột tới chấm vuông ở góc dưới bên phải ô, khi con trỏ thành dấu cộng đen mảnh thì kéo xuống ô F3.

Ô F2 hiện 12,000 và ô F3 hiện 42,000
Mã SP03 có đơn giá 12.000 và mã SP05 có đơn giá 42.000

Ví dụ 2: Bạn tra cứu theo hàng

Hàm LOOKUP tự nhận biết vùng nằm ngang, nên bạn không cần đổi sang hàm khác. Bạn chọn ô B6 và gõ công thức sau:

=LOOKUP(B5,B2:E2,B3:E3)

Bạn đang gõ vào ô B6 công thức LOOKUP theo hàng, ô B5 chứa số tháng 3
Hàm tìm số 3 ở dòng 2 và lấy giá trị cùng cột ở dòng 3

Bạn nhấn Enter. Ô B6 hiện 410, đúng với doanh thu của Tháng 3.

Ví dụ 3: Bạn dùng dạng mảng

Dạng mảng ngắn hơn vì bạn chỉ khai báo một vùng. Bạn chọn ô F2 và gõ công thức sau:

=LOOKUP(E2,A2:C5)

Bạn đang gõ vào ô F2 công thức =LOOKUP(E2,A2:C5), bảng A2:C5 được tô viền màu
Hàm tìm mã ở cột đầu của bảng và lấy kết quả ở cột cuối

Bạn nhấn Enter. Ô F2 hiện 35.000, là đơn giá của mã SP04. Dạng này không cho bạn chọn cột kết quả, nên bạn chỉ dùng khi cột cần lấy nằm ở cuối bảng và bảng có số dòng không ít hơn số cột.

Ví dụ 4: Bạn lấy ô cuối cùng có dữ liệu

Đây là ứng dụng hay gặp nhất của hàm LOOKUP. Sổ quỹ ghi số dư mỗi ngày, có ngày bỏ trống, và bạn cần số dư mới nhất.

Bạn chọn ô D2 và gõ công thức sau:

=LOOKUP(2,1/(B2:B7<>""),B2:B7)

Bạn đang gõ vào ô D2 công thức lấy ô cuối cùng có dữ liệu của cột B
Công thức ngắn nhưng có một mẹo ở giữa, phần dưới sẽ giải thích

Bạn nhấn Enter. Ô D2 hiện 18.250.000, đúng với dòng cuối cùng có số dư.

Ô D2 hiện số dư 18,250,000, cột B có một ô trống ở ngày 04/10
Ô trống ở ngày 04/10 không làm công thức sai

Vùng B2:B7 là vùng cố định. Khi sổ quỹ có thêm dòng, bạn mở rộng vùng trong công thức, ví dụ =LOOKUP(2,1/(B2:B1000<>""),B2:B1000).

Phần công thức

Phần này làm gì

B2:B7<>""

Phép so sánh trả về TRUE ở ô có dữ liệu và FALSE ở ô trống.

1/(...)

Phép chia cho ra 1 ở ô có dữ liệu và lỗi #DIV/0! ở ô trống, vì TRUE tính là 1 và FALSE tính là 0.

LOOKUP(2,...)

Hàm tìm số 2 nhưng vùng chỉ có số 1, nên hàm lấy số 1 cuối cùng và bỏ qua các lỗi. Số tìm phải lớn hơn 1 thì mẹo này mới đúng.

B2:B7 ở cuối

Vùng này là nơi hàm lấy giá trị ở đúng vị trí vừa tìm được.

Ví dụ 5: Bạn tìm tháng gần nhất có doanh số

Mẹo ở ví dụ 4 cũng dùng được theo chiều ngang. Bạn chọn ô G2 và gõ công thức sau:

=LOOKUP(2,1/(B2:F2<>""),$B$1:$F$1)

Bạn đang gõ vào ô G2 công thức tìm tháng gần nhất có doanh số của nhân viên An
Vùng kết quả là dòng tiêu đề tháng, nên hàm trả về tên tháng

Vùng tiêu đề có dấu $ để giữ nguyên khi bạn kéo xuống, còn B2:F2 không có dấu $ để đổi theo từng dòng. Bạn nhấn Enter, chọn lại ô G2 rồi kéo xuống ô G5. Dòng không có doanh số nào sẽ ra lỗi #N/A.

Cột G hiện Tháng 10, Tháng 9, Tháng 11 và Tháng 7 cho 4 nhân viên
Mỗi nhân viên có tháng gần nhất khác nhau, và công thức tự tìm đúng

Ví dụ 6: Bạn thay IF lồng nhau khi xếp loại

Khi có nhiều mốc, công thức IF lồng nhau rất dài và khó sửa. Hàm LOOKUP làm việc này ngắn hơn nhờ cách tìm gần đúng.

Bảng mốc ở cột E và F đã xếp tăng dần. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:

=LOOKUP(B2,$E$2:$E$5,$F$2:$F$5)

Bạn đang gõ vào ô C2 công thức LOOKUP xếp loại theo bảng mốc ở cột E và F
Hàm lấy mốc lớn nhất không vượt quá điểm của học viên

Bạn chọn lại ô C2 rồi kéo xuống ô C6.

Cột Xếp loại hiện Yếu, Trung bình, Khá, Giỏi, Giỏi cho 5 học viên
Điểm 5 đúng bằng mốc nên được xếp Trung bình, còn điểm 6,8 lấy mốc 6,5 nên được xếp Khá

Muốn đổi mốc, bạn sửa số ở cột E và F, đồng thời giữ thứ tự tăng dần. Nếu thêm mốc mới, bạn mở rộng vùng trong công thức. Nếu bạn cần so sánh với cách dùng hàm IF, bạn có thể đọc bài hàm IF nhiều điều kiện trong Excel.

Bạn nên dùng LOOKUP hay VLOOKUP?

Việc bạn cần làm

Hàm nên dùng

Lý do

Bạn tra đúng mã trong bảng chưa sắp xếp.

VLOOKUP với tham số FALSE

LOOKUP cần vùng xếp tăng dần và luôn tìm gần đúng.

Bạn lấy ô cuối cùng có dữ liệu.

LOOKUP

Công thức ngắn và không cần hàm mới.

Bạn xếp loại theo các mốc.

LOOKUP

Công thức ngắn và dễ sửa mốc.

Bạn có thể xem thêm bài hàm VLOOKUP trong Excel để nắm cách tra cứu chính xác.

Bạn muốn học Excel bài bản hơ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.