Công thức Excel: Tra cứu đối sánh chính xác với SUMPRODUCT
Công thức Excel: Tra cứu đối sánh chính xác với SUMPRODUCT

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 là không phải trường hợp nhạy cảm. 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 làm cần đượ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 sao kép”) để buộc 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 đi kèm với 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 trong số các trận đấu đó. Đây có thể là điều bạn muốn hoặc không, vì vậy hãy cẩn thận nếu bạn mong đợi nhiều trận đấu!

Hãy nhớ rằng, công thức này chỉ hoạt động với 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.

https://exceljet.net/formula/exact-match-lookup-with-sumproduct