Excel VLOOKUP all variations

Excel VLOOKUP is a very common and popular function. It is now superseded with the XLOOKUP function. Still it’s popularity is not going down. This video (in English and Hindi) covers all variations of VLOOKUP.

Excel VLOOKUP variations

We are covering only the single VLOOKUP function usage. We are NOT covering nested VLOOKUP or combination of various lookup functions. I decided to focus on these fundamental variations. This is because, once you know these variations, you can combine them as needed.

VLOOKUP take four parameters –

1. What to look for
2. Where to look for it
3. Which column to return if you find the item
4. Whether this is a range lookup

The fourth parameter is tricky – and optional! Therefore most people using Excel VLOOKUP just leave it blank and hope for the best. This can lead to serious errors.

Range_lookup parameter

The function tries to search for the item in the first column of lookup range. If it finds the item, it returns the value from the specified column.

We can control how VLOOKUP behaves if it does NOT find the item.

We can ask it to just return an error – #NA

OR

We can ask it to read in-between-the-lines. This is where the last parameter comes in.

Although it is not very intuitive, Excel VLOOKUP can look for a match between the items shown in the lookup table.

Whether it is a number or text, this method works.

In fact, The last parameter Range_Lookup – is actually a question. Excel is asking us a question –

“If I do not find the exact value, what should I do?”

If you want it to read in between the previous and next range value, say YES – which in Excel’s language means 1 or True.

If you do not want it to look in the range, then say NO – 0 or False.

The variations

It is much easier to watch the video and learn this concept. There are four variations. Number or text and Exact or approximate match.

Although this sounds confusing, the examples shown in the video will make it crystal clear in your mind.

Remember to read up XLOOKUP as well.

Excel VLOOKUP alternative – Data model relationships

The most common use of VLOOKUP is for decoding master – transaction table code and description. You can eliminate this completely with Power Pivot. Import both tables into Power Pivot Data Model and create a relationship. That’s it.

Use Tables with Excel Vlookup()

This is very important. The lookup table is bound to increase over time. If you do not use tables, you must change the ranges in all VLOOKUP formulas manually. You may forget to do this.

If you use tables, you can refer to the table name in the second parameter – table_array – to solve this problem. Now, when you add more rows to the lookup table, individual VLOOKUP formulas do not need to change at all.

***