Microsoft Excel (Bài 84): Hàm LOOKUP tìm giá trị và công thức ví dụ trong Excel

Cách sử dụng đơn giản nhất của hàm Lookup trong Excel là tìm 1 giá trị trong 1 cột hoặc 1 hàng và trả về giá trị ở vị trí tương ứng trong hàng hoặc cột chứa giá trị cần trả về. Có 2 kiểu sử dụng hàm Lookup đó là kiểu Vector và kiểu mảng

SỬ DỤNG HÀM LOOKUP TRONG EXCEL – KIỂU VECTOR

Khi sử dụng hàm Lookup, Vector được hiểu là 1 hàng hay 1 cột trong Excel. Cú pháp của hàm Lookup đơn giản như sau:

=LOOKUP( < giá trị cần tìm kiếm >, < vector chứa giá trị cần tìm kiếm >,[ vector chứa kết quả trả về ] )

Trong đó:

• < giá trị cần tìm kiếm > (bắt buộc): giá trị bạn đang cần tìm kiếm, có thể là dạng số, dạng text, dạng đúng / sai hoặc tham chiếu đến ô chưa giá trị cần tìm kiếm.
• < vector chứa giá trị cần tìm kiếm > (bắt buộc): 1 dòng hoặc 1 cột chứa giá trị cần tìm kiếm. Bắt buộc phải được sắp xếp tăng dần (A->Z hoặc nhỏ nhất đến lớn nhất)
• [ vector chứa kết quả trả về ] (không bắt buộc): 1 dòng hoặc 1 cột chứa giá trị trả về. Vector này cần có kích thước giống như kích thước của vector chứa giá trị cần tìm kiếm, nếu không có vector này, thì vector chứa giá trị cần tìm kiếm sẽ được dùng để thay thế.

2 Ví dụ sau đây sẽ giúp bạn hiểu rõ hơn về cú pháp phía trên của hàm Lookup trong Excel

TRA CỨU DỮ LIỆU THEO CỘT VỚI HÀM LOOKUP TRONG EXCEL

=LOOKUP(B2,D2:D5,E2:E5)

Để hiểu rõ hơn, bạn có thể xem hình sau đây:

TRA CỨU DỮ LIỆU THEO HÀNG VỚI HÀM LOOKUP TRONG EXCEL

=LOOKUP(B2,B6:E6,B7:E7)

LƯU Ý KHI TRA CỨU SỬ DỤNG HÀM LOOKUP TRONG EXCEL

1. Vector chứa giá trị cần tra cứu cần được sắp xếp theo thứ tự từ điển (A đến Z) hoặc sắp xếp từ nhỏ tới lớn, nếu không công thức Lookup sẽ bị lỗi hoặc kết quả sẽ bị sai. Nếu trong trường hợp nào đó bạn không thể sắp xếp dữ liệu mà vẫn cần tra cứu, hãy sử dụng hàm Index kết hợp với Match
2. Vector chứa giá trị cần tra cứu và vector kết quả là hàng hoặc cột có kích thước giống nhau
3. Hàm Lookup trong Excel không phân biệt chữ hoa và chữ thường khi tra cứu
4. Hàm Lookup sử dụng chế độ tìm kiếm gần đúng, nếu không tìm thấy giá trị đúng 100% như giá trị đang cần tìm kiếm, nó sẽ tìm đến giá trị X lớn nhất trong vector chứa giá trị tìm kiếm với điều kiện X nhỏ hơn hoặc bằng giá trị cần tìm kiếm. Ví dụ: Nếu bạn tìm giá trị 5 mà 5 không được tìm thấy, Lookup sẽ tìm tiếp giá trị 4, nếu 4 không được tìm thấy, Lookup sẽ tìm kiếm các giá trị nhỏ hơn tiếp theo.
5. Nếu giá trị tìm kiếm nhỏ hơn giá trị nhỏ nhất trong vector chứa giá trị tìm kiếm, hàm Lookup sẽ trả về lỗi #N/A

SỬ DỤNG HÀM LOOKUP TRONG EXCEL – KIỂU MẢNG

Hàm Lookup theo kiểu mảng sẽ tìm kiếm giá trị ở cột đầu tiên trong mảng và trả về giá trị ở vị trí tương ứng trong cột cuối cùng của mảng đó. Cú pháp:

=LOOKUP(< giá trị cần tìm kiếm >, < mảng dùng để tìm kiếm >)

với:

• < giá trị cần tìm kiếm > (bắt buộc): giá trị tìm kiếm trong mảng
• < mảng dùng để tìm kiếm > (bắt buộc): mảng chưa giá trị cần tìm kiếm và giá trị trả về. Giá trị trong mảng cần được sắp xếp theo thứ tự tăng dần

Lưu ý:

1. Nếu mảng dùng để tra cứu có số dòng lớn hơn hoặc bằng số cột (như ảnh phía trên) thì hàm Lookup sẽ tra cứu theo cột
2. Nếu mảng dùng để tra cứu có số dòng nhỏ hơn số cột, thì hàm Lookup sẽ tra cứu theo dòng
3. Nếu giá trị cần tìm không được tìm thấy, Lookup sẽ dùng giá trị lớn nhất trong mảng mà nhỏ hơn hoặc bằng giá trị cần tìm
4. Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong hàng đầu tiên hoặc cột đầu tiên của mảng, lỗi #N/A sẽ được trả về
5. Học Excel Online không khuyên dùng hàm Lookup ở dạng mảng này. Bạn hãy sử dụng hàm VLOOKUP hoặc HLOOKUP

ỨNG DỤNG CỦA HÀM LOOKUP TRONG EXCEL:

TÌM GIÁ TRỊ TRONG Ô CUỐI CÙNG CHỨA DỮ LIỆU CỦA 1 CỘT

Nếu dữ liệu của bạn thay đổi thường xuyên và bạn cần tham chiếu tới giá trị cuối cùng trong 1 cột ở bảng dữ liệu đó, đây là trường hợp mà hàm LOOKUP dạng vector sẽ toả sáng. Ví dụ: Tìm giá trị trong ô cuối cùng có chứa dữ liệu ở cột A:

=LOOKUP(2,1/(A:A<>””), A:A)

Ví dụ tiếp theo:

Diễn giải công thức:

1. Phần (A:A<>””) sẽ so sánh mỗi ô trong cột A với chuỗi rỗng – “”. Sự so sánh này có kết quả là TRUE nếu ô không rỗng và ngược là là FALSE nếu ô rỗng. Trong hình trên, kết quả sẽ như sau: {TRUE; TRUE; TRUE; TRUE; FALSE; …}
2. Tiếp theo 1/(A:A<>””) sẽ cho chúng ta kết quả là {1;1;1;1;#DIV/0;…}
3. Số 2 chắc chắn sẽ không được tìm thấy trong mảng kết quả của 1/(A:A<>””), vì logic của Lookup là nếu không tìm thấy giá trị sẽ chuyển sang kiểu tìm kiếm gần đúng và kiểu tìm kiếm gần đùng này sẽ lấy giá trị cuối cùng trong các số 1 ở ví dụ trên, tương ứng với vị trí số 1 này ở vector kết quả trả về sẽ là giá trị ở trong ô dữ liệu cuối cùng của cột A

=> Nếu bạn muốn lấy dòng cuối cùng có chứa dữ liệu trong 1 cột với hàm Lookup, công thức rất đơn giản sẽ là:

=LOOKUP(2,1/(A:A<>””), ROW(A:A))

TÌM GIÁ TRỊ CỦA Ô CUỐI CÙNG CHỨA DỮ LIỆU TRONG 1 HÀNG

Cũng với logic như trên, công thức để tìm kiếm giá trị ô cuối cùng chưa dữ liệu trong 1 hàng như sau:

=LOOKUP(2,1/(1:1<>””),1:1)

Công thức sẽ tìm giá trị của ô cuối cùng có chứa dữ liệu trong hàng số 1, để tìm giá trị của ô cuối cùng chưa dữ liệu trong hàng n, công thức như sau:

=LOOKUP(2,1/(n:n<>””),n:n)

THÁNG CÓ SALES GẦN NHẤT CỦA NHÂN VIÊN SALES

Với những kiến thức đã trình bày ở trên về Lookup, chúng ta có thể dễ dàng giải quyết được câu hỏi bằng công thức sau đây:

=LOOKUP(2,1/(B2:G2=”x”),$B$1:$G$1)

THAY THẾ HÀM IF LỒNG NHAU TRONG EXCEL

Một ứng dụng thú vị khác nữa của hàm LOOKUP đó chính là việc thay thế hàm IF lồng nhau trong trường hợp sau đây

B2 = LOOKUP(A2,{“c”;”d”;”t”},{“Contemporary”;”Discontinue”;”Temporary”})

C2 = IF(A2=”C”,”Contemporary”,IF(A2=”D”,”Discontinue”,IF(A2=”T”,”Temporary”,””)))