Today we are going to discuss about VLOOKUP Function in Excel. Instead of going into the details of the syntax etc., we shall discuss how we use it search a given list of items, from a big pool of data. We will see if the items in the list have any match in the pool, or not.
Ideally the ‘Ctrl + F’ feature saves us the pain of finding something in the excel sheet. But if the data to be matched is large enough (say, 300 items to be matched against a database of 17,000), you’re dead! This is where VLOOKUP() and HLOOKUP(), come to the rescue.
Let us take an example, where I need to search ten items from a pool of hundred items.
Note: The data pool is in the column ‘B2:B101’, and the items to be searched are in the column ‘C2:C11’.
To see if ‘Demo 18’ (1st item) is in the Data Pool or not – type in the cell D2 – “=VLOOKUP(C2,$B$2:$B$101,1,FALSE)“.
If the items match correctly, it would give the name of item that was searched against the data pool. If we copy-paste the results for all the items, we would get a result like this.
If you notice, all the items that are a mismatch to the data pool are shown as ‘#N/A’.
In this way, we can sort out all the odd items, which do not match against the data pool.
Now let’s play around with this concept a little bit. Basically, you get ‘#N/A’, because VLOOKUP gives an error in case of a mismatch.
If we use ‘IFERROR’ function to display a specific message in cells, where there is a mismatch, such as D6 – “=IFERROR(VLOOKUP(C6,$B$2:$B$101,1,FALSE), “NOT PRESENT”)”
We will get a result –
And a little bit of conditional formatting would highlight all the odd ones.
