Công thức Excel: XLOOKUP khớp với bất kỳ cột nào
Công thức Excel: XLOOKUP khớp với bất kỳ cột nào

Tóm lược

Để thực hiện tra cứu bằng cách so khớp một giá trị trong bất kỳ một trong số các cột, bạn có thể sử dụng công thức dựa trên Hàm XLOOKUP cùng với Chức năng MMULT. Trong ví dụ được hiển thị, công thức trong K6 là:

=XLOOKUP(1,MMULT(--(codes=K5),SEQUENCE(COLUMNS(codes),1,1,0)),group)

Ở đâu mã số (C5: H15) và nhóm (B5: B15) là phạm vi được đặt tên. Kết quả cho mã tra cứu “BDBC” là “Epsilon. Việc tra cứu này cũng có thể được thực hiện trong các phiên bản Excel cũ hơn với INDEX và MATCH, như giải thích bên dưới.

Giải trình

Trong ví dụ này, chúng ta có một bảng chứa 6 cột mã và mỗi hàng mã thuộc một nhóm trong cột B. Mục tiêu là tra cứu bất kỳ mã nào trong C5: H15 và trả về tên của nhóm mà mã đó thuộc về. đến. Thách thức là mã có thể nằm trong một trong sáu cột bất kỳ và có thể có nhiều cột hơn trong tập dữ liệu lớn hơn. Công thức trong K6 là:

=XLOOKUP(1,MMULT(--(codes=K5),SEQUENCE(COLUMNS(codes),1,1,0)),group)

Ở đâu mã số (C5: H15) và nhóm (B5: B15) là phạm vi được đặt tên.

Ở cấp độ cao, công thức này sử dụng Hàm XLOOKUP để thực hiện tra cứu, với số 1 là giá trị tra cứu và phạm vi được đặt tên nhóm như mảng trả về. Phần phức tạp của công thức là mảng tra cứu, được tạo bằng Chức năng MMULT như thế này:

MMULT(--(codes=K5),SEQUENCE(COLUMNS(codes),1,1,0))

Hàm MMULT thực hiện phép nhân ma trận, đây là một cách tiện dụng để giảm kết quả từ nhiều cột xuống một cột kết quả duy nhất. MMULT có hai mảng, array1array2và yêu cầu số cột trong array1 giống với số hàng trong array2. Ma trận kết quả (là một mảng) sẽ có cùng số hàng với ma trận đầu tiên và cùng số cột với ma trận thứ hai.

Mảng đầu tiên chỉ đơn giản là phép kiểm tra logic của tất cả các mã trong phạm vi được đặt tên C5: I13 so với mã mà chúng ta đang tìm kiếm trong K5:

=MMULT(--(codes=K5)

Các Âm kép buộc các giá trị TRUE và FALSE thành 1s và 0s. Kết quả là một mảng gồm 11 hàng x 6 cột. Lưu ý rằng mã 1 duy nhất trong hàng 5 là mã phù hợp duy nhất:

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

Mảng đầu tiên có 6 cột, vì vậy mảng thứ hai phải chứa 6 hàng. Các Hàm SEQUENCE cung cấp một cách dễ dàng để tạo mảng này, một số trợ giúp từ Hàm COLUMNS:

SEQUENCE(COLUMNS(codes),1,1,0)

COLUMNS trả về 6 cho SEQUENCE vì hàng tranh luận. Các cột đối số là 1, khởi đầu là 1, và bươc giá trị bằng không. Kết quả là một mảng có 6 hàng và 1 cột, chỉ được điền bằng 1:

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

Sau đó, hàm MMULT sẽ tính tích ma trận của hai mảng và trả về một mảng có 11 hàng và 1 cột:

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

Lưu ý hàng 5, chứa mã “BDBC” là 1, trong khi tất cả các hàng khác là 0. Mảng này được trả về XLOOKUP dưới dạng lookup_array:

=XLOOKUP(1,{0;0;0;0;1;0;0;0;0;0;0},group)

và XLOOKUP đối sánh với mục 1 và trả về mục thứ 5 trong nhóm, “Epsilon”

Không có XLOOKUP và SEQUENCE

Trong các phiên bản Excel không có XLOOKUP và SEQUENCE, vấn đề này có thể được giải quyết bằng công thức dựa trên INDEX và MATCH:

{=INDEX(group,MATCH(1,MMULT(--(codes=K5),TRANSPOSE(COLUMN(codes)^0)),0))}

Lưu ý: đây là một công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.

Hàm MMULT được cấu hình để trả về kết quả tương tự như trên, nhưng cú pháp được sử dụng để tạo array2 khác:

TRANSPOSE(COLUMN(codes)^0))

Các Hàm COLUMN trả về 6 số trong một mảng ngang:

COLUMN(data) // returns {3,4,5,6,7,8}

Và những con số này sau đó được nâng lên thành lũy thừa 0 với toán tử lũy thừa (^):

COLUMN(data)^0) // returns {1,1,1,1,1,1}

Nâng bất kỳ số nào lên lũy thừa của không (0) sẽ cho kết quả là 1, vì vậy kết quả là mảng 1 x 6 giống nhau chỉ được điền bằng 1s.

Cuối cùng, VẬN CHUYỂN lật mảng thành mảng 1 x 6 thành mảng 6 x 1:

TRANSPOSE({1,1,1,1,1,1} // returns {1;1;1;1;1;1}

và kết quả được chuyển cho hàm MMULT là array2, như trước.

Mảng đầu tiên trong MMULT được tạo theo cách giống như trong công thức gốc, với cùng kết quả. Vì vậy, kết quả cuối cùng từ MMULT là giống nhau:

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

Thay mảng này vào công thức, chúng ta có một công thức INDEX và MATCH “chuẩn”:

=INDEX(group,MATCH(1,{0;0;0;0;1;0;0;0;0;0;0},0))

Các Hàm MATCH trả về 5, vị trí của 1 duy nhất trong mảng:

=INDEX(group,5) // returns "Epsilon"

MỤC LỤC trả về mục thứ 5 trong phạm vi đã đặt tên nhóm (B5: B15), “Epsilon”, là kết quả cuối cùng.

https://exceljet.net/formula/xlookup-match-any-column