Kết hợp hàm INDEX và hàm MATCH trong excel là một trong những ứng dụng mạnh nhất khi sử dụng hàm. Và hàm Index Match được biết tới phổ biến nhất với tính năng thay thế hàm Vlookup vô cùng hoàn hảo. Trong khi hàm Vlookup được biết đến là một hàm tham chiếu và tìm kiếm vô cùng mạnh thì vẫn có những trường hợp không thể thực hiện được ngay cả khi kết hợp nó với một hàm nào khác.

Bạn đang xem: Cách dùng hàm index và match trong excel

Sự giới hạn về vị trí của đối số tham chiếu trong hàm Vlookup đã được khắc phục hoàn toàn với sự kết hợp giữa hàm Match và Index. Trong bài viết này, ADO sẽ đưa tới những bài tập, ứng dụng của hàm Match Index.


XEM NHANH

1 Hàm INDEX và hàm MATCH2 Ứng dụng hàm INDEX kết hợp MATCH

Hàm INDEX và hàm MATCH

Hàm INDEX là hàm trả về giá trị theo vị trí của hàng và cột trong một vùng dữ liệu. Trong khi hàm MATCH lại là hàm trả về vị trí tương đối của 1 giá trị trong một dải ô, vùng dữ liệu.

Chính vì vậy mà chúng có thể kết hợp hoàn hảo với nhau, kết quả của hàm Match trở thành đầu vào của hàm Index. Và cuối cùng sẽ giúp bạn tìm ra giá trị trả về dựa theo một giá trị khác.

Cú pháp hàm INDEX và hàm MATCH

Cú phám phàm INDEX

=INDEX(Array,Row_num,)

Trong đó:

Array: Là vùng ô hoặc một hằng số mảng nào đó bắt buộc.Row_num: Là chọn hàng trong mảng từ đó trả về một giá trị.Column_num: Là chọn cột trong mảng từ đó trả về một giá trị.

Cú pháp hàm Match

=MATCH(lookup_value, lookup_array, )

Trong đó:

Lookup_value: Là giá trị tìm kiếm trong mảng Lookup_array. Giá trị này có thể là số, văn bản, giá trị logic hoặc một tham chiếu ô đến một số, văn bản hay giá trị logic, bắt buộc phải có.Lookup_array: Là mảng hay phạm vị ô được tìm kiếm, bắt buộc có.Match_type: Kiểu tìm kiếm, có thể có hoặc không (-1,0,1)

Ứng dụng hàm INDEX kết hợp MATCH

Tìm thông tin của một người theo thông tin nhập vào

Cách sử dụng hàm Index và hàm Match trong excel để tìm kiếm dữ liệu là giải pháp phổ biến nhất. Trong bài tập ví dụ này chúng ta cần tìm kiếm mức lương theo họ và tên trong danh sách.

Nguyên tắc sẽ là chúng ta sử dụng hàm Match để tìm vị trí dòng tương ứng với tên nhân viên, sau đó chỉ cần dóng cùng dòng đó sang và xác định số thứ tự cột trong vùng dữ liệu với hàm Index để có kết quả chính xác.

Đầu tiên, công thức: =MATCH(C12,B1:B9,0) để giúp chúng ta tìm kiếm chính xác vị trí dòng của Trần Đức Thuỳ trong cột Họ và Tên. Kết quả trả về là 5. Vậy là bạn đã có thông số Row_num là 5.

Chúng ta cần tìm mức lương nên cột Mức lương sẽ là số 6 – Column_num (tương ứng với vùng chọn từ B1 tới G9 trong ví dụ).

*

Công thức: =INDEX(B1:G9,MATCH(C12,B1:B9,0),6) sẽ trả về giá trị là giao của dòng 5 và cột 6 trong vùng chọn B1:G9 là 15,000,000.

Ở ví dụ này chúng ta tìm kiếm theo vị trí từ trái qua phải, tức là cột chứa giá trị trả về nằm bên phải cột chứa giá trị dò tìm. Trong trường hợp thì hàm Vlookup cũng có thể giúp các bạn trả về kết quả chuẩn mà không cần dùng 2 hàm.

Xem thêm: Top 3 Cách Xuống Dòng Trong 1 Ô Excel 2007 2010 2013 2016, Các Cách Xuống Dòng Trong Excel Dễ Nhất

Công thức bạn có thể dùng =VLOOKUP(C12,B1:G9,6,FALSE)

Vậy thì trong trường hợp nào hàm Index Match vượt trội hơn hẳn Vlookup? Chúng ta hãy cùng thực hành trường hợp tiếp theo.

Dò tìm từ phải qua trái

Trường hợp cần tìm dữ liệu ở cột bên trái của cột chứa thông tin tra cứu/tìm kiếm. Cụ thể ở đây là chúng ta tìm Họ tên, Email của một nhân viên theo số điện thoại của nhân viên đó.

Công thức áp dụng hoàn toàn không có gì khác biệt so với ví dụ trên. Chúng ta vẫn kết hợp hai hàm với thứ tự lồng hàm Match bên trong như vậy. Cơ chế hoạt động cũng giữ nguyên.

Tìm vị trí dòng của số điện thoại nhập vào trong cột số điện thoại. Bạn cần tìm họ tên và email thì đã xác định được số cột và chỉ cần dòng cùng dòng sang để ra kết quả.

Công thức của chúng ta sẽ là: =INDEX($B$1:$G$9,MATCH($C$15,$D$1:$D$9,0),1)

Kết quả trả về sẽ là tên “Trần Đức Thuỳ” tương ứng với số điện thoại mà chúng ta nhập vào. Với thông tin Email cũng tương tự. Chúng ta chỉ cần đổi phần đối số Column_num của hàm Index từ 1 thành 2. Và email trả sẽ là LDthuy1995
gmail.com.

Ở đấy các bạn chú ý, để tránh mất thời gian gõ lại công thức thì ở công thức vừa trên, chúng ta đã cố định các vùng chọn cũng như dữ liệu dò tìm (SĐT trong ô C15). Nên việc copy công thức xuống các ô dưới sẽ thuận tiện và nhanh hơn rất nhiều. Bạn chỉ cần sửa lại 1 đối số làm xong.

*

Ngoài ra bạn cũng có thể tìm kiếm đồng thời thêm cả mức lương như ví dụ đầu tiên, và kết quả đương nhiên vẫn là 15,000,000.

Tìm kiếm dữ liệu theo nhiều điều kiện với hàm Match và Index

Trong ứng dụng này chúng cần tìm một mức giá bất kỳ của một mã chứng khoán bất kỳ trong bảng. Điều này của nghĩa chúng ta tìm kết quả dựa vào hai điều kiện là: Mã CK và loại Mức giá.

Vậy thì hàm Index match trong trường hợp sẽ kết hợp như thế nào?

*

Nguyên tắc thực hiện sẽ như sau: Bạn sẽ xác định dòng chứa loại Mã CK tương ứng trong ô điều kiện 1. Tiếp theo sẽ xác định cột chưa loại Mức giá trong ô điều kiện 2. Sau đó lấy giao của cột và hàng để ra giá trị kết quả mà chúng ta mong muốn.

Không như ứng dụng đầu tiên, thành phần điều kiện thứ hai – Mức giá của chúng ta không xác định được và phù thuộc vào giá trị mà chúng ta chọn (4 loại).

Nên thay vì xác định được luôn vị trí cột thì chúng ta cũng phải sử dụng hàm MATCH để tìm giá vị trí cột tương đối của loại Mức giá trong vùng chọn.

Bước 1. Xác định vị trí dòng của “Mã CK” với công thức: =MATCH(B16,A2:A10,0)

Bước 2. Xác định vị trí cột của loại giá “Sàn” với công thức: =MATCH(B17,A2:E2,0)

*

Bước 3. Kết quả trả về của hai hàm MATCH sẽ là đầu vào của hàm INDEX cho 2 đối số lần lượt là Row_num và Column_num.

Công thức sẽ là: =INDEX(A2:E10,MATCH(B16,A2:A10,0),MATCH(B17,A2:E2,0))

Và kết qủa của chúng ta sẽ là 111,2 – là giao của dòng số 5 với cột số 4 trong vùng chọn A2:E10.

Kết luận hàm Index Match

Trên đây là ví dụ ứng dụng cũng như những giải thích chi tiết về các dùng hàm INDEX và hàm MATCH trong excel. Bạn hoàn toàn có thể ứng dụng ngay vào công việc của mình với những kiến thức vô cùng hữu ích này. ADO tin chắc sự kết hợp hàm index và match sẽ giúp bạn giải quyết nhiều vấn đề bao gồm cả việc có thể thay thế hoàn hảo cho hàm Vlookup.