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
Bản thâ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 chữ hoa chữ thường, chúng tôi sử dụng hàm EXACT như sau:
EXACT(B5:B15,"RED") // test for "RED"
Vì có 11 giá trị trong phạm vi E5: D15, EXACT trả về một mảng có 11 kết quả TRUE FALSE như sau:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Lưu ý vị trí của TRUE tương ứng với hàng có màu "ĐỎ".
Để ngắn gọn (và để dễ dàng mở rộng logic 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 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 bằng cách đặt đối số chế độ tìm kiếm cho XLOOKUP thành -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.
