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:
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))
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:
You can download the file related to this article from the link below:
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