Công thức chung
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Tóm lược
Tra cứu phân biệt chữ hoa chữ thường trong Excel
Theo mặc định, tra cứu tiêu chuẩn trong Excel không phân biệt chữ hoa chữ thường. Cả hàm VLOOKUP và INDEX / MATCH sẽ chỉ trả về kết quả khớp đầu tiên, bỏ qua chữ hoa chữ thường.
Một cách trực tiếp để giải quyết hạn chế này là sử dụng công thức mảng dựa trên INDEX / MATCH với EXACT. Tuy nhiên, nếu bạn chỉ tìm kiếm các giá trị số, SUMPRODUCT + EXACT cũng cung cấp một cách thú vị và linh hoạt để thực hiện tra cứu phân biệt chữ hoa chữ thường.
Trong ví dụ, chúng tôi đang sử dụng công thức sau
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Mặc dù công thức này là một công thức mảng, nó không cần phải được nhập bằng Control + Shift + Enter, vì SUMPRODUCT xử lý mảng nguyên bản.
Giải trình
SUMPRODUCT được thiết kế để làm việc với các mảng, nó nhân lên rồi tính tổng.
Trong trường hợp này, chúng ta là hai mảng với SUMPRODUCT: B3: B8 và C3: C8. Mẹo là chạy kiểm tra các giá trị trong cột B, sau đó chuyển đổi các giá trị TRUE / FALSE kết quả thành 1 và 0. Chúng tôi chạy thử nghiệm với EXACT như vậy:
EXACT(E3,B3:B8)
Cái nào tạo ra mảng này:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Lưu ý rằng giá trị thực ở vị trí 3 là giá trị khớp của chúng ta. Sau đó, chúng tôi sử dụng âm kép (tức là -, về mặt kỹ thuật là "một ngôi kép") để ép các giá trị TRUE / FALSE này thành 1 và 0. Kết quả là mảng này:
(0; 0; 1; 0; 0; 0)
Tại thời điểm này trong phép tính, công thức SUMPRODUCT trông giống như sau:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT sau đó chỉ cần nhân các mục trong mỗi mảng với nhau để tạo ra một mảng cuối cùng:
(0; 0; 775; 0; 0; 0)
Sau đó SUMPRODUCT sẽ tính tổng và trả về 775.
Vì vậy, ý chính của công thức này là các giá trị FALSE được sử dụng để loại bỏ tất cả các giá trị khác. Những giá trị duy nhất tồn tại là những giá trị ĐÚNG.
Lưu ý rằng vì chúng tôi đang sử dụng SUMPRODUCT, công thức này có một bước ngoặt duy nhất: nếu có nhiều kết quả phù hợp, hàm SUMPRODUCT sẽ trả về tổng của các kết quả phù hợp đó. Đây có thể là điều bạn muốn, vì vậy hãy cẩn thận nếu bạn có nhiều trận đấu!
Hãy nhớ rằng, công thức này chỉ hoạt động cho các giá trị số, vì SUMPRODUCT không xử lý văn bản. Nếu bạn muốn truy xuất văn bản, hãy sử dụng INDEX / MATCH + EXACT.
