Công thức Excel: Kết hợp gần đúng với nhiều tiêu chí
Công thức Excel: Kết hợp gần đúng với nhiều tiêu chí

Tóm lược

Để tra cứu và so khớp gần đúng dựa trên nhiều tiêu chí, bạn có thể sử dụng công thức mảng dựa trên INDEX và MATCH, với sự giúp đỡ từ Hàm IF. Trong ví dụ được hiển thị, công thức trong G8 là:

{=INDEX(D5:D10,MATCH(G7,IF(B5:B10=G6,C5:C10),1))}

Mục tiêu của công thức này là trả về “kích thước” khi cho một con vật và trọng lượng.

Lưu ý: đây là một công thức mảng và phải được nhập bằng Control + Shift + Enter

Giải trình

Về cơ bản, đây chỉ là một công thức INDEX / MATCH. Vấn đề trong trường hợp này là chúng ta cần “sàng lọc” các mục nhập không liên quan trong bảng để chúng ta chỉ còn lại các mục nhập tương ứng với động vật mà chúng ta đang tìm kiếm.

Điều này được thực hiện với một hàm IF đơn giản ở đây:

IF(B5:B10=G6,C5:C10)

Đoạn mã này kiểm tra các giá trị trong B5: B10 để xem chúng có khớp với giá trị trong G6 (động vật) hay không. Khi có sự trùng khớp, các giá trị tương ứng trong C5: C11 được trả về. Trường hợp không có kết quả phù hợp FALSE được trả về. Khi G6 chứa “cat”, mảng kết quả sẽ giống như sau:

{FALSE; FALSE; FALSE; 5; 10; 15}

Điều này đi vào Hàm MATCH dưới dạng mảng. Giá trị tra cứu cho đối sánh đến từ G7, chứa trọng lượng (trong ví dụ là 9 lbs).

Lưu ý rằng đối sánh được định cấu hình cho đối sánh gần đúng bằng cách đặt match_type thành 1 và điều này yêu cầu các giá trị trong C5: C11 phải được sắp xếp.

MATCH trả về vị trí của trọng số trong mảng và giá trị này được chuyển cho Hàm INDEX như số hàng. Lookup_array cho INDEX là các kích thước trong D5: D10, vì vậy INDEX trả về kích thước tương ứng với vị trí được tạo bởi MATCH (số 4 trong ví dụ được hiển thị).

https://exceljet.net/formula/approximate-match-with-multiple-criteria