Công thức Excel: Nhận trận đấu thứ n với INDEX / MATCH -

Công thức chung

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Tóm lược

Để truy xuất nhiều giá trị phù hợp từ một tập hợp dữ liệu có công thức, bạn có thể sử dụng hàm IF và SMALL để tìm ra số hàng của mỗi kết quả khớp và cung cấp giá trị đó trở lại INDEX. Trong ví dụ được hiển thị, công thức trong I7 là:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Trong đó các dải ô được đặt tên là amts (D4: D11), id (I3) và id (C4: C11).

Lưu ý rằng đây là 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, công thức này chỉ đơn giản là một công thức INDEX lấy giá trị trong một mảng ở một vị trí nhất định. Giá trị của n được cung cấp trong cột H và tất cả công việc "nặng nhọc" mà công thức thực hiện là tìm ra hàng để lấy giá trị, trong đó hàng tương ứng với khớp "thứ n".

Hàm IF thực hiện công việc tìm ra hàng nào chứa kết quả khớp và hàm SMALL trả về giá trị thứ n từ danh sách đó. Bên trong IF, kiểm tra logic là:

ids=id

tạo ra mảng này:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Lưu ý rằng id khách hàng khớp ở vị trí thứ 1 và thứ 4, xuất hiện dưới dạng ĐÚNG. Đối số "giá trị nếu đúng" trong IF tạo ra một danh sách các số hàng tương đối với biểu thức này:

ROW(ids)-ROW(INDEX(ids,1,1))+1

tạo ra mảng này:

(1;2;3;4;5;6;7)

Mảng này sau đó được "lọc" bởi các kết quả kiểm tra lôgic và hàm IF trả về kết quả mảng sau:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Lưu ý rằng chúng tôi có số hàng hợp lệ cho hàng 1 và hàng 2.

Mảng này sau đó được xử lý bởi SMALL, được cấu hình để sử dụng các giá trị trong cột H để trả về giá trị "thứ n". Hàm SMALL tự động bỏ qua các giá trị logic TRUE và FALSE trong mảng. Cuối cùng, các công thức giảm thành:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Xử lý lỗi

Khi không còn kết quả nào phù hợp với một id đã cho, hàm SMALL sẽ trả về lỗi #NUM. Bạn có thể xử lý lỗi này bằng hàm IFERROR hoặc bằng cách thêm logic để đếm kết quả khớp và hủy xử lý khi số trong cột H lớn hơn số lần khớp. Ví dụ ở đây cho thấy một cách tiếp cận.