Công thức Excel: XLOOKUP phân biệt chữ hoa chữ thường
Công thức Excel: XLOOKUP phân biệt chữ hoa chữ thường

Công thức chung
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
Tóm lược

Để tạo đối sánh chính xác có phân biệt chữ hoa chữ thường, bạn có thể sử dụng hàm XLOOKUP với hàm EXACT. Trong ví dụ được hiển thị, công thức trong F5 là:

=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)

khớp với “RED” (phân biệt chữ hoa chữ thường) và trả về toàn bộ hàng.

Giải trình

Tự nó, Hàm XLOOKUP không phân biệt chữ hoa chữ thường. Giá trị tra cứu của “RED” sẽ khớp với “red”, “RED” hoặc “Red”. Chúng ta có thể khắc phục hạn chế này bằng cách xây dựng một mảng tra cứu phù hợp cho XLOOKUP với một biểu thức logic.

Làm việc từ trong ra ngoài, để cung cấp cho XLOOKUP khả năng đối sánh trường hợp, chúng tôi sử dụng Hàm EXACT như thế này:

EXACT(B5:B15,"RED") // test for "RED"

Vì có 11 giá trị trong phạm vi E5: D15, EXACT trả về giá trị mảng với 11 kết quả TRUE FALSE như thế này:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Chú ý vị trí của TRUE tương ứng với hàng có màu “ĐỎ”.

Để ngắn gọn (và cho phép logic dễ dàng được mở rộng với logic boolean), chúng tôi buộc các giá trị TRUE FALSE thành 1s và 0s với Âm kép:

--EXACT(B5:B15,"RED") // convert to 1s and 0s

tạo ra một mảng như thế này:

{0;0;0;0;1;0;0;0;0;0;0}

Chú ý vị trí của 1 tương ứng với hàng có màu “ĐỎ”. Mảng này được trả về trực tiếp cho hàm XLOOKUP dưới dạng đối số mảng tra cứu.

Bây giờ chúng ta có thể đơn giản là công thức để:

=XLOOKUP(1,{0;0;0;0;1;0;0;0;0;0;0},B5:D15)

Với giá trị tra cứu là 1, XLOOKUP tìm giá trị 1 ở vị trí thứ 5 và trả về hàng thứ 5 trong mảng trả về, B9: D9.

Mở rộng logic

Cấu trúc của logic có thể dễ dàng mở rộng. Ví dụ: để thu hẹp đối sánh thành “ĐỎ” trong tháng 4, bạn có thể sử dụng công thức như sau:

=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)

Ở đây, vì mỗi một trong hai biểu thức trả về một mảng các giá trị TRUE FALSE và vì các mảng này được nhân với nhau, nên phép toán buộc các giá trị TRUE và FALSE thành 1s và 0s. Không nhất thiết phải sử dụng phủ định kép.

Vì giá trị tra cứu vẫn là 1, như trong công thức ở trên.

Trận đấu đầu tiên và cuối cùng

Cả hai công thức trên sẽ trả về kết quả khớp đầu tiên của “RED” trong một tập dữ liệu. Nếu bạn cần kết quả khớp cuối cùng, bạn có thể thực hiện tra cứu ngược lại bằng cách đặt đối số chế độ tìm kiếm cho XLOOKUP đến 1:

=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match

Nếu bạn cần trả về kết quả từ nhiều trận đấu, hãy xem Chức năng FILTER.

https://exceljet.net/formula/xlookup-case-sensitive