Which VLOOKUP Formula

By Mynda Treacy of My Online Training Hub

One of the first Excel formulas you’re likely to learn is VLOOKUP.

If you’ve used it before you probably remember the day when you discovered it.

You might have even got a buzz from the new power you had at your fingertips (go on, admit it), when all of a sudden Excel became much more than a glorified calculator.

But VLOOKUP is one of many lookup functions in Excel’s arsenal, and it might shock you to know that it’s not always the best!

I feel almost as though I’m cheating on VLOOKUP saying that, after all it is my all-time favourite formula. Not because it’s the best, but it was one of the first formulas I learnt. And because VLOOKUP is so versatile I probably over used it too.

 

Excel LOOKUP Functions

VLOOKUP

Looks up a value in the leftmost column of a range and returns a value in the same row from a column you specify.

VLOOKUP - Which VLOOKUP Formula

Tip: Sort your data in ascending order to make it more efficient for Excel to process.

Learn VLOOKUP formulas.

HLOOKUP

Same as VLOOKUP but looks across rows instead of down columns.

Learn HLOOKUP formulas.

INDEX & MATCH

Ok, strictly speaking these are two functions, but when put together they are more efficient and flexible than VLOOKUP or HLOOKUP. Plus they have the added advantage of being able to get around VLOOKUP’s limitation of not being able to return a value to the left of the lookup column.

INDEX & MATCH - Which VLOOKUP Formula

Learn INDEX & MATCH formulas.

SUMIF or SUMIFS

'What?' I hear you say. Yes, you read correctly. If you want to return a number from your lookup and there’s only one possible match, you can use SUMIF. And if you want to lookup based on multiple criteria you can use SUMIFS. Plus with these functions you can return values to the left of your ‘lookup’ column.

SUMIF - Which VLOOKUP Formula

Learn SUMIF and SUMIFS formulas.

LOOKUP

Lastly the original LOOKUP function is provided for backward compatibility. It requires the list to be sorted in ascending order otherwise it’ll return erroneous data.

 

LOOKUP - Which VLOOKUP Formula

 

The Best Lookup Function

Which Lookup is best depends on where you’re at with your Excel skills.

For beginners: VLOOKUP is the best, hands down.

It’s fairly easy to understand and remember. Once you master it you can nest it with other functions like COLUMNS to make it dynamic, or CHOOSE to extend its capabilities and get around some of the limitations.

If you already know VLOOKUP then tackle INDEX and MATCH.

This power combination will open new options for analysing your data. It’s more efficient than VLOOKUP so if your workbook is slow try INDEX and MATCH instead.

Plus it can allow for matrix matches where you need to lookup both the row and column to find your match.

Don’t forget to keep SUMIFS up your sleeve for those times when you need to lookup multiple criteria and you’re only returning one number.

Bonus tip: for Excel 2007 onwards you can hide the errors returned by these lookups when a match isn’t found, like #N/A for VLOOKUP by using the IFERROR function. It’s far more efficient than the old IF(ISNA trick we used in Excel 2003 days.

Mynda Treacy

Mynda Treacy is co-founder of My Online Training Hub and author of their popular Excel blog, and comprehensive Excel Formulas library.

If you would like to learn from her you can. She shares her knowledge in her online Excel Expert course and her widely acclaimed Excel Dashboard course.