Many of us use Excel on a regular basis and have come to rely on certain key functions: Pivot Tables, Removing Duplicates, Sorting/Filtering, Conditional Formatting, and VLOOKUP. All are great functions that allow you to work efficiently with your data.
The frustrating thing about VLOOKUP is that it only provides a resulting data point "to the right" of your source data element. So if your source data item is in Column F, your array to find must typically be on or to the right of Column G. This works great -- until you have a case when you need to find data on the left. Consider the following data set of customer information:
If we were given a CustomerID (Col B) and needed to return an EIN (Col G), we could certainly do that with VLOOKUP.
But what if we were given the EIN of 28-8573644 and needed to return a CustomerID? VLOOKUP is no longer your friend. Instead, look at IndexMatch.
IndexMatch uses the following format:
= INDEX (OUTPUT_COLUMN, MATCH (VALUE, INPUT_COLUMN,0))
= INDEX (B:B,MATCH ("28-8573644",G:G,0))
= QEX711
Of course you can reference the cell of the source value. So if the EIN was in cell O2, you could alter the following as: = INDEX (B:B,MATCH (O2,G:G,0))
The fun thing about IndexMatch is that it works both "to the left" as well as "to the right," so it can replace VLOOKUP altogether if you like.
Comments