Using VLOOKUP() in Excel to locate a list of items in a given pool of data

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.

You can download a sample file here.