Excel INDEX MATCH Functions

You might have seen a lot of places talking about the INDEX and MATCH functions. The purpose of using the INDEX and MATCH functions together is to create a functionality similar to VLOOKUP. The problem with the VLOOKUP function is that it can only search the first column of a table of data. So for example lets say we have the data below:

Example 1 Excel MATCH function

Lets say we want to search for a name and return the corresponding age. In cell F1 the user will input a name, the age associated with that name will be displayed in cell F2:

=INDEX(C2:C7, MATCH(G1, B2:B7, 0))

Excel, INDEX, MATCH functions
Result:

Example 1 Excel MATCH function, Result
This could have also been achieved using the following VLOOKUP function in cell G2:

=VLOOKUP(G1, B2:C7, 2, FALSE)

But what if the data was in this format:

Excel, Data
We would no longer be able to use the VLOOKUP function to lookup the age based on the name. This is because VLOOKUP will only search the first column of a table for the target value. In this case we would have to use the INDEX, MATCH combination:

Example 2 Excel MATCH, INDEX
Result:

Example 2 Excel MATCH, INDEX, Result. png
You can download the file related to this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published.