Tra Cứu Gần Đúng Với INDEX Và MATCH Trong Excel Và Google Sheets

Tra cứu gần đúng với INDEX và MATCH chính là một trong những bằng chứng rõ ràng nhất về tính linh hoạt của hàm INDEX và MATCH trong Excel, khi nó có thể tìm kiếm kết quả phù hợp nhất với yêu cầu của bạn khi trong bảng dữ liệu không có kết quả chính xác, tính năng này đặc biệt hữu dụng khi bạn làm việc với các kiểu dữ liệu khó xác định rõ ràng, đầy đủ như số thập phân,…

Bước thêm một bước tiến tí xíu nữa, để thực hiện tính năng này, chúng ta sẽ kết hợp thêm hai hàm cơ bản khác đó là MIN và ABS để tạo giá trị tra cứu và mảng tra cứu cho các đối số của hàm MATCH. Nói ngắn gọn, chúng ta sẽ sử dụng MATCH để tìm ra sự khác biệt nhỏ nhất, sau đó sử dụng INDEX để truy xuất kết quả. Nào chúng ta cùng bắt đầu thôi!

Bạn đang xem: Tra Cứu Gần Đúng Với INDEX Và MATCH Trong Excel Và Google Sheets

Tra cứu gần đúng với INDEX và MATCH trong Excel 365, 2019

Cú pháp tra cứu gần đúng với INDEX và MATCH

=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)) // Tùy cài đặt máy, mà dấu ngăn cách sẽ là dấu phẩy (,) hay dấu chắm phẩy (;)

Chúng ta cùng phân tích kỹ hơn về các đối số cần truyền và cách sử dụng công thức trên để tra cứu gần đúng trong Excel. Để tìm kết quả khớp gần nhất trong dữ liệu số, bạn có thể sử dụng INDEX và MATCH, với sự trợ giúp từ các hàm ABS và MIN.

Trong ví dụ trên, công thức trong F5 là:

=INDEX(chuyến đi,MATCH(MIN(ABS(chi phí-E5)),ABS(chi phí-E5),0)) 

Công thức chuẩn sẽ có chuyến đi là B5:B14 và chi phí là C5:C14, mình ghi như vậy trong công thức ở trên nhầm các bạn dễ hiểu được cách vận hành của công thức, còn để sử dụng các bạn phải đưa mảng cụ thể vào ô đối số. Ô F5, F6 và F7 sẽ cần kết quả trả là CHUYẾN ĐI có chi phí gần nhất với lần lượt là 500, 1000 và 1500.

Về cơ bản, đây vẫn là công thức kết hợp INDEX và MATCH thông thường: MATCH định vị vị trí của kết quả khớp gần nhất, cung cấp vị trí cho INDEX và INDEX trả về giá trị tại vị trí đó trong cột “Chuyến đi”. Bước khó nhất là cách cài đặt công thức bên trong hàm MATCH để phù hợp với “sự khác biệt tối thiểu” như sau:

MATCH(MIN(ABS(chi phí-E5)),ABS(chi phí-E5),0)

Hiểu theo từng bước, từ trong ra ngoài, sẽ là:

(chi phí-E5)

Đầu tiên, chúng ta sẽ tính sự chênh lệch của chi phí ở ô E5 và tất cả các chi phí của các chuyến đi khác bằng cách lấy từng ô trong chi phí (C5:C14) cho ô E5. Công thức này sẽ trả về một mảng 10 giá trị (tương ứng 10 ô từ C5:C14):

{899;199;250;-201;495;1000;450;-101;500;795}

Tiếp theo, chúng ta sẽ thêm hàm ABS vào:

ABS({899;199;250;-201;495;1000;450;-101;500;795})

Xem thêm : Hàm XOR Trong Excel Và Google Sheets

Hàm ABS có tác dụng lấy giá trị tuyệt đối của các tham số truyền vào, kết quả sau khi sử dụng hàm ABS, mảng 10 phần tử ban đầu trở thành:

{899;199;250;201;495;1000;450;101;500;795}

Bởi vì, chúng ta muốn tìm giá trị gần đúng, tức là giá trị có chênh lệch NHỎ NHẤT, nên chúng ta sẽ dùng hàm MIN để tìm giá trị nhỏ nhất trong mảng 10 phần tử trên, kết quả trả về sẽ là 101:

MIN({899;199;250;201;495;1000;450;101;500;795}) // trả về 101

Kết quả trên sẽ trở thành lookup_value cho hàm MATCH. Đối với lookup_array trong hàm MATCH, ta cũng thực hiện tương tự:

ABS(chi phí-E5) // tạo mảng tra cứu

Kết quả trả về sẽ là mảng giá trị tuyệt đối của hiệu từng chi phí với ô E5, như chúng ta đã phân tích lúc nãy:

{899;199;250;201;495;1000;450;101;500;795}

Lúc này, công thức:

MATCH(MIN(ABS(chi phí-E5)),ABS(chi phí-E5),0)

có thể hiểu thành:

MATCH(101,{899;199;250;201;495;1000;450;101;500;795},0) // tìm vị trí giá trị 101 trong mảng 10 phần tử

Kết quả trả về sẽ là 8 (101 là giá trị thứ 8 trong mảng trên. Cuối cùng, vị trí này sẽ được đưa vào INDEX dưới dạng đối số hàng, lookup_array sẽ là mảng “chuyến đi” (B5:B14):

=INDEX(chuyến đi,8) // tìm giá trị ở vị trí thứ 8 trong mảng "chuyến đi"

Và hàm INDEX ở trên sẽ trả về kết quả là giá trị của ô thứ 8 trong phạm vi các chuyến đi (B5:B14): “Tây Ban Nha”. Tương tự khi công thức được sao chép xuống các ô F6, F7, nó sẽ tìm thấy kết quả khớp gần nhất với 1000 và 1500 là “Pháp” và “Thái Lan” như ảnh dưới, lưu ý nếu có nhiều kết quả giống nhau, công thức sẽ trả về kết quả đầu tiên trùng khớp:

Tra cứu gần đúng với INDEX và MATCH trong Excel 2016

Cách tra cứu gần đúng với INDEX và MATCH trong Excel 2016 tương tự với Excel 2019, các bạn có thể tham khảo ở trên.

Tra cứu gần đúng với INDEX và MATCH trong Excel 2013

Xem thêm : Hàm CONCAT Trong Excel Và Google Sheets

Cách tra cứu gần đúng với INDEX và MATCH trong Excel 2013 tương tự với Excel 2019, các bạn có thể tham khảo ở trên.

Tra cứu gần đúng với INDEX và MATCH trong Excel 2010

Cách tra cứu gần đúng với INDEX và MATCH trong Excel 2010 tương tự với Excel 2019, các bạn có thể tham khảo ở trên.

Tra cứu gần đúng với INDEX và MATCH trong Excel 2007

Cách tra cứu gần đúng với INDEX và MATCH trong Excel 2007 tương tự với Excel 2019, các bạn có thể tham khảo ở trên.

Tra cứu gần đúng với INDEX và MATCH trong Excel 2003

Cách tra cứu gần đúng với INDEX và MATCH trong Excel 2003 tương tự với Excel 2019, các bạn có thể tham khảo ở trên.

Tra cứu gần đúng với INDEX và MATCH trong Google Sheets

Cách tra cứu gần đúng với INDEX và MATCH trong Google Sheets tương tự với Excel 2019, các bạn có thể tham khảo ở trên.

n so với hàm VLOOKUP và các hàm tra cứu khác?

Không chỉ tiện dụng, linh hoạt, mà việc sử dụng kết hợp INDEX + MATCH không có các hạn chế cứng ngắc như một số hàm khác, ví dụ như VLOOKUP.

Nếu sử dụng riêng lẻ, thì hai hàm này có còn tốt không?

Câu trả lời là có, và điều này còn tùy thuộc vào mục địch và cách bạn sử dụng hai hàm này nữa. Chi tiết về cách sử dụng từng hàm riêng lẻ các bạn có thể tham khảo thêm trong hai bài viết “Hàm MATCH trong Excel” và “Hàm INDEX trong Excel”.

Lời kết

Qua bài viết này, mình đã giới thiệu và hướng dẫn các bạn sử dụng tính năng tra cứu gần đúng với INDEX và MATCH – một tính năng hữu dụng khi giá trị bạn muốn tìm không có kết quả chính xác trong mảng tra cứu! Mình mong là qua bài viết này sẽ hữu ích và hỗ trợ được bạn trong học tập, công việc hàng ngày!

Tất cả kiến thức mình chia sẻ đều thông qua quá trình học hỏi và thực hành của mình nên nếu có thiếu sót mong các bạn bỏ qua. Mọi ý kiến thắc mắc hay đóng góp về bài viết đều đáng giá với mình, các bạn có thể để lại bên dưới phần bình luận!

Cảm ơn các bạn đã đọc bài viết! Chúc các bạn thành công!

File sử dụng trong bài viết: tra-cuu-gan-dung-voi-index-va-match.xlsx

Nguồn: https://25giay.vn
Danh mục: Excel