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:

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))


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:

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:

Result:

You can download the file related to this article from the link below:

• Excel MATCH INDEX function.xlsx

See also:

• Excel VLOOKUP vs INDEX and MATCH Speed Comparison
• Excel, MATCH() function 
• Excel INDEX() Function
• Match function in VBA

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