Công thức Excel: Thứ tự giảm dần INDEX và MATCH -

Công thức chung

=INDEX(range1,MATCH(lookup,range2,-1))

Tóm lược

Để truy xuất các giá trị từ bảng trong đó các giá trị tra cứu được sắp xếp theo thứ tự giảm dần (ZA), bạn có thể sử dụng INDEX và MATCH, với MATCH được định cấu hình cho đối sánh gần đúng bằng cách sử dụng loại đối sánh -1. Trong ví dụ được hiển thị, công thức trong F5 là:

=INDEX(C5:C9,MATCH(F4,B5:B9,-1))

trong đó các giá trị trong B5: B9 được sắp xếp theo thứ tự giảm dần.

Bối cảnh

Giả sử bạn có một sản phẩm được bán ở dạng cuộn 100 feet và chỉ được phép đặt hàng ở dạng nguyên cuộn. Ví dụ: nếu bạn cần 200 feet vật liệu, bạn cần tổng cộng hai cuộn và nếu bạn cần 275 feet, bạn sẽ cần mua ba cuộn. Trong trường hợp này, bạn muốn công thức trả về bậc "cao nhất tiếp theo" bất cứ khi nào bạn vượt qua bội số chẵn của 100.

Giải trình

Công thức này sử dụng -1 cho loại đối sánh để cho phép đối sánh gần đúng trên các giá trị được sắp xếp theo thứ tự giảm dần. Phần MATCH của công thức trông giống như sau:

MATCH(F4,B5:B9,-1)

Sử dụng giá trị tra cứu trong ô F4, MATCH tìm giá trị đầu tiên trong B5: B9 lớn hơn hoặc bằng giá trị tra cứu. Nếu tìm thấy kết quả khớp chính xác, MATCH trả về số hàng tương đối cho kết quả khớp đó. Khi không tìm thấy kết quả khớp chính xác, MATCH tiếp tục qua các giá trị trong B5: B9 cho đến khi tìm thấy giá trị nhỏ hơn, sau đó nó "lùi lại" và trả về số hàng trước đó.

Trong ví dụ được hiển thị, giá trị tra cứu là 275, vì vậy MATCH trả về số hàng là 3 thành INDEX: