Cách sử dụng XLOOKUP() với nhiều tiêu chí trong Excel

XLOOKUP() trong Excel hiện là hàm mới nhất trong nhóm hàm tra cứu (LOOKUP(), VLOOKUP(), HLOOKUP() và XLOOKUP()) với nhiều ưu điểm, chức năng mở rộng và tính linh hoạt cao hơn.

Trong hướng dẫn này, trước hết chúng ta sẽ bàn về mục đích của hàm XLOOKUP() và lý do nó vượt trội hơn các hàm tra cứu cũ; tiếp theo, chúng ta xem cú pháp cơ bản của hàm, rồi đi thẳng vào trọng tâm — cách dùng hàm XLOOKUP() với nhiều tiêu chí.

Nếu bạn cần học các kiến thức nền tảng của Excel, khóa học thân thiện cho người mới bắt đầu Introduction to Excel là điểm khởi đầu phù hợp.

TL;DR

• XLOOKUP() là hàm tra cứu mới nhất của Excel. Nó linh hoạt và nhanh hơn VLOOKUP(), HLOOKUP() và LOOKUP(), hỗ trợ tra cứu đa hướng và khớp một phần.

• Để tra cứu với nhiều tiêu chí, có hai cách chính: nối chuỗi (ghép các giá trị tra cứu bằng &, ví dụ =XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)) hoặc biểu thức boolean (nhân các điều kiện, ví dụ =XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)).

• Nối chuỗi đơn giản và dễ đọc hơn, nhưng biểu thức boolean cho bạn nhiều linh hoạt hơn.

• Các tham số tùy chọn mở rộng hàm hơn nữa: [if_not_found] trả về văn bản tùy chỉnh khi không khớp, [match_mode] cho phép khớp gần đúng hoặc ký tự đại diện, và [search_mode] cho phép đảo chiều tìm kiếm.

Vì sao nên dùng XLOOKUP() trong Excel

Hàm XLOOKUP() tìm trong một phạm vi hoặc mảng dữ liệu và trả về mục tương ứng với kết quả khớp đầu tiên. Nếu không tìm thấy khớp, XLOOKUP() có thể trả về khớp gần đúng nếu cung cấp kiểu khớp cụ thể. Ở nhiều khía cạnh, hàm XLOOKUP() vượt trội so với các tiền nhiệm trong Excel (VLOOKUP(), HLOOKUP() và LOOKUP()).

Cụ thể, nó cho phép:

• tìm dữ liệu theo cả chiều ngang và dọc, theo bất kỳ hướng nào
• nhiều tiêu chí tìm kiếm
• khớp gần đúng, mặc định là khớp chính xác
• khớp một phần
• trả về nhiều cột và hàng
• trả về văn bản tùy chỉnh khi không tìm thấy khớp.

Ngoài ra, hàm XLOOKUP() chạy nhanh hơn các hàm tra cứu cũ trong Excel, điều này quan trọng khi tìm kiếm trong lượng dữ liệu lớn.

Cách dùng XLOOKUP() với một điều kiện

Hãy điểm qua nhanh cú pháp cơ bản của hàm XLOOKUP():

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

trong đó các tham số bắt buộc là:

• lookup_value—giá trị cần tìm

• lookup_array—mảng hoặc phạm vi để tìm

• return_array—mảng hoặc phạm vi để trả về

và các tham số tùy chọn là:

• [if_not_found]—văn bản cung cấp để trả về khi không có khớp

• [match_mode]—kiểu khớp (chính xác hoặc gần đúng), và trả về gì nếu không có khớp.

• [search_mode]—chế độ tìm kiếm (trực tiếp hoặc ngược, tìm kiếm nhị phân trên mảng tra cứu đã sắp xếp tăng dần hoặc giảm dần).

Chúng ta sẽ xem ví dụ sử dụng các tham số tùy chọn này ở phần sau của hướng dẫn.

Để học hoặc ôn lại các hàm và công thức hữu ích khác trong Excel, bạn có thể xem hướng dẫn 15 công thức Excel cơ bản ai cũng cần biết và Excel Formulas Cheat Sheet.

Cách dùng XLOOKUP() với nhiều tiêu chí

Như thấy từ cú pháp cơ bản, hàm XLOOKUP() trong Excel được thiết kế mặc định để làm việc với một giá trị tra cứu của một biến xác định.

Tuy nhiên, ta có thể điều chỉnh hàm này để dùng đồng thời cho nhiều biến, tìm giá trị tra cứu khác nhau ở mỗi biến. Nói cách khác, ta có thể thực hiện tìm kiếm bằng XLOOKUP() với nhiều tiêu chí. Có hai cách chính: nối chuỗi và biểu thức boolean; chúng ta sẽ lần lượt bàn về cả hai.

Trước khi đi vào chi tiết kỹ thuật, hãy xem một bảng Excel mà chúng ta sẽ làm việc. Bảng này cung cấp thông tin về 10 con mèo, gồm tên, màu lông và tuổi:

Trong các ví dụ, chúng ta sẽ dùng XLOOKUP() để tìm một trong ba đặc điểm của mèo bằng hai đặc điểm còn lại.

Không sao nếu bảng rất đơn giản và ta có thể tự nhìn ra kết quả. Ở đây, mục tiêu là hiểu nguyên lý cốt lõi khi dùng XLOOKUP() với nhiều tiêu chí. Nắm được nguyên lý này, ta có thể dễ dàng mở rộng cho các tình huống phức tạp thực tế, ví dụ khi có hơn hai tiêu chí tìm kiếm hoặc có yêu cầu cụ thể.

XLOOKUP() với nhiều tiêu chí bằng cách nối chuỗi

Cách nối chuỗi khá thẳng thắn: ta cần nối các giá trị tra cứu và các mảng tra cứu tương ứng với nhau. Hãy xem cách hoạt động.

Giả sử ta muốn tìm tuổi của một con mèo tên Nala có màu trắng–xám. Để tiện lợi, ta thêm hai tiêu chí này trên cùng trang tính chứa bảng:

Trong trường hợp này, công thức XLOOKUP() sẽ như sau:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Nó sẽ trả về 2.5, tức tuổi của con Nala màu trắng–xám đầu tiên (mèo của tôi 😺). Hãy nhớ XLOOKUP() trả về mục tương ứng với kết quả khớp đầu tiên.

Để hiểu hai thành phần đầu của công thức trên — các phần có dấu ampersand — ta có thể chạy riêng từng phần trong một ô Excel:

=B2:B11&C2:C11

Kết quả là NalaWhite và Grey.

=B2:B11&C2:C11

Kết quả là:

Đúng vậy, rất đơn giản: ta chỉ cần nối các giá trị tra cứu và mảng tra cứu tương ứng với nhau, theo đúng cú pháp cơ bản của XLOOKUP() đã nói ở trên.

XLOOKUP() với nhiều tiêu chí bằng biểu thức boolean

Giờ hãy thực hiện cùng nhiệm vụ — tìm tuổi của mèo Nala màu trắng–xám — bằng cách thứ hai: biểu thức boolean. Khi đó, công thức XLOOKUP() sẽ là:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Đúng như mong đợi, nó cũng trả về 2.5, tuổi của con Nala trắng–xám đầu tiên.

Hãy phân tích các thành phần của công thức trên. Ở đây, 1 nghĩa là TRUE, tức chúng ta đang tìm giá trị TRUE trong mảng tra cứu biểu diễn bởi thành phần thứ hai của công thức — (B2:B11=G2)*(C2:C11=G3).

Thành phần này gồm hai thừa số, mỗi thừa số kiểm tra một điều kiện: thừa số thứ nhất kiểm tra tên có khớp tên cần tìm không, thừa số thứ hai kiểm tra màu có khớp màu cần tìm không. Hãy chạy mỗi thừa số trong một ô Excel riêng:

=B2:B11=G2

Kết quả là:

=C2:C11=G3

Kết quả là:

Giờ hãy chạy toàn bộ thành phần thứ hai của công thức:

=(B2:B11=G2)*(C2:C11=G3)

Kết quả là:

Dù có hai giá trị 1 trong kết quả trên (tức hai giá trị TRUE), hàm XLOOKUP() trả về mục tương ứng với kết quả khớp đầu tiên.

Cách dùng biểu thức boolean khi chạy XLOOKUP() cho nhiều tiêu chí trông phức tạp và kém trực quan hơn so với nối chuỗi. Điều này còn tệ hơn nếu dùng quá hai tiêu chí. Vậy tại sao không luôn dùng nối chuỗi?

Phương pháp biểu thức boolean cho ta linh hoạt hơn nhiều khi dùng XLOOKUP() với nhiều tiêu chí. Hãy xem các ưu điểm của nó so với nối chuỗi.

Kiểm tra giá trị FALSE

Trong công thức trên, ta kiểm tra biểu thức boolean là TRUE bằng cách truyền giá trị 1:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Đôi khi, ta cần kiểm tra biểu thức boolean là FALSE. Ví dụ, muốn tìm tuổi của con mèo đầu tiên trong bảng không phải là Nala trắng–xám. Ta chỉnh công thức như sau:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Kết quả sẽ là 1, tức tuổi của con mèo đầu tiên không phải Nala trắng–xám (chính xác là Nala tam thể, không phải trắng–xám).

Dùng các toán tử logic

Với biểu thức boolean, ta không chỉ giới hạn ở kiểm tra bằng nhau. Giả sử muốn tìm màu lông của một con Nala có tuổi nhỏ hơn 2:

Trong trường hợp này, công thức XLOOKUP() sẽ là:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Kết quả sẽ là Tricolor.

Thỏa mãn ít nhất một tiêu chí

Từ trước tới giờ, ta đang kiểm tra sự phù hợp với tất cả tiêu chí đưa ra. Ở tình huống khác, ta có thể cần thỏa mãn ít nhất một tiêu chí.

Để xem cách hoạt động, hãy quay lại nhiệm vụ ban đầu — tìm tuổi của mèo Nala màu trắng–xám:

Lần này, ta muốn tìm tuổi của một con mèo hoặc tên Nala hoặc màu trắng–xám. Công thức XLOOKUP() sẽ là:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Nó trả về 1, tức tuổi của một con Nala tam thể.

Nhắc lại, khi tìm tuổi của con mèo vừa tên Nala vừa trắng–xám, công thức là:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Việc thay * bằng + tạo nên khác biệt.

Nếu bạn muốn khám phá toàn bộ tiềm năng của Excel, hãy cân nhắc lộ trình kỹ năng Excel Fundamentals toàn diện, thực hành và cân bằng.

Tham số tùy chọn cho XLOOKUP() với nhiều tiêu chí

Giống như khi dùng XLOOKUP() với một tiêu chí, ta có thể sử dụng các tùy chọn bổ sung khi chạy với nhiều tiêu chí. Đây là lúc các tham số tùy chọn [if_not_found], [match_mode] và [search_mode] phát huy tác dụng.

Hãy xem nhanh các ví dụ dùng từng tham số này với XLOOKUP() và nhiều tiêu chí. Để đơn giản, chúng ta áp dụng cách nối chuỗi trong mỗi ví dụ.

Trả về văn bản cung cấp khi không tìm thấy khớp

Ở đây ta tìm tuổi của một con Nala màu đen — mèo không tồn tại trong bảng:

Trong trường hợp này, chúng ta thêm tham số tùy chọn [if_not_found] vào công thức XLOOKUP() và gán văn bản trả về khi không có khớp:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Quả thực, hàm trả về văn bản đã cung cấp: No cat is found. Nếu không cung cấp văn bản, hàm sẽ trả về #N/A.

Trả về khớp gần đúng

Giả sử ta tìm tuổi của một con Nala tam thể, nhưng không chắc màu được ghi là "Tricolor" hay "Tricolour" trong bảng. Khi đó, cần dùng khớp gần đúng dựa trên ký tự đại diện và truyền đối số [match_mode] bằng 2 cho hàm XLOOKUP().

Trong bảng trên, chúng ta dùng dấu hoa thị (*), đại diện cho bất kỳ số lượng ký tự nào, bao gồm 0. Các ký tự đại diện khác có trong tài liệu của Microsoft Office.

Công thức XLOOKUP() ở đây sẽ là:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

Và nó sẽ trả về 1, tức tuổi của con Nala tam thể đầu tiên (và duy nhất) trong bảng.

Lưu ý rằng đối số 2 trong công thức trên có nghĩa là chúng ta quan tâm đến khớp gần đúng, chứ không phải tìm tuổi của mèo Nala có màu "Tricolo*r." Trong trường hợp của chúng ta, dấu hoa thị khớp từ "Tricolor," nhưng cũng sẽ hoạt động với "Tricolour" hoặc, chẳng hạn, "Tricolooor."

Thực hiện tìm kiếm ngược

Hãy làm lại bài tập ban đầu — tìm tuổi của Nala màu trắng–xám — nhưng lần này, chúng ta bắt đầu tìm từ mục cuối cùng.

Để làm vậy, cần thêm tham số tùy chọn [search_mode] đặt bằng -1 vào hàm XLOOKUP() như dưới đây:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

Hàm trả về 7, tức tuổi của con Nala trắng–xám đầu tiên tính từ cuối mảng tra cứu.

XLOOKUP() so với INDEX() và MATCH()

Trong các phiên bản Excel cũ, để tái tạo chức năng tương tự dùng XLOOKUP() với nhiều tiêu chí, ta cần kết hợp hai hàm INDEX() và MATCH(). Không đi sâu vào cú pháp của cả hai, hãy xem cách tìm tuổi của con Nala trắng–xám đầu tiên bằng phương pháp cũ INDEX() + MATCH() và bằng hàm XLOOKUP() mới (tham chiếu ô xem ở bảng trước):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Dù các đối số trông có phần tương tự, hãy điểm qua ưu điểm chính khi chạy XLOOKUP() so với phương pháp cũ:

• Chỉ cần một hàm cho thao tác này — không cần kết hợp hàm.

• Có thể áp dụng cách nối chuỗi khi phù hợp.

• Có thể dùng các tham số tùy chọn (hàm MATCH() không có).