Cách sử dụng kết hợp hàm INDEX và Hàm MATCH trong excel – ADO – Học viện đào tạo kỹ năng Ado Việt Nam

Chia sẻ bài viết tới bè bạn

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.

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.

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ì thế mà chúng hoàn toàn có thể phối hợp tuyệt đối với nhau, hiệu quả của hàm Match trở thành nguồn vào của hàm Index. Và ở đầu cuối 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,[Column_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, [match_type])

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 tích 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 tài liệu là giải pháp phổ cập nhất. Trong bài tập ví dụ này tất cả chúng ta cần tìm kiếm mức lương theo họ và tên trong list .
Nguyên tắc sẽ là tất cả 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 cấp dưới, sau đó chỉ cần dóng cùng dòng đó sang và xác lập số thứ tự cột trong vùng tài liệu với hàm Index để có tác dụng đúng mự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 tất cả 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 hoàn toàn có thể giúp những bạn trả về tác dụng chuẩn mà không cần dùng 2 hàm .

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 tiêu biểu vượt trội hơn hẳn Vlookup ? Chúng ta hãy cùng thực hành thực tế 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 vận dụng trọn vẹn không có gì độc lạ so với ví dụ trên. Chúng ta vẫn tích 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 giải trí cũng giữ nguyên .
Tìm vị trí dòng của số điện thoại cảm ứng nhập vào trong cột số điện thoại thông minh. Bạn cần tìm họ tên và email thì đã xác lập được số cột và chỉ cần dòng cùng dòng sang để ra hiệu 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à [email protected]

Ở đấy những bạn quan tâm, để tránh mất thời hạn gõ lại công thức thì ở công thức vừa trên, tất cả chúng ta đã cố định và thắt chặt những vùng chọn cũng như tài liệu dò tìm ( SĐT trong ô C15 ). Nên việc copy công thức xuống những ô 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 tài liệu theo nhiều điều kiện kèm theo 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ã sàn chứng khoán bất kể trong bảng. Điều này của nghĩa tất cả chúng ta tìm hiệu quả dựa vào hai điều kiện kèm theo là : Mã CK và loại Mức giá .
Vậy thì hàm Index match trong trường hợp sẽ phối hợp như thế nào ?

Nguyên tắc triển khai sẽ như sau : Bạn sẽ xác lập dòng chứa loại Mã CK tương ứng trong ô điều kiện kèm theo 1. Tiếp theo sẽ xác lập cột chưa loại Mức giá trong ô điều kiện kèm theo 2. Sau đó lấy giao của cột và hàng để ra giá trị hiệu quả mà tất cả chúng ta mong ước .
Không như ứng dụng tiên phong, thành phần điều kiện kèm theo thứ hai – Mức giá của tất cả chúng ta không xác lập được và phù thuộc vào giá trị mà tất cả chúng ta chọn ( 4 loại ) .
Nên thay vì xác lập được luôn vị trí cột thì tất cả 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.

Chia sẻ bài viết tới bạn hữu

Source: http://wp.ftn61.com
Category: Tin Tức