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
Looks up a value in the leftmost column of a range and returns a value in the same row from a column you specify.
Tip: Sort your data in ascending order to make it more efficient for Excel to process.
Learn VLOOKUP formulas.
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.
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.
Learn SUMIF and SUMIFS formulas.
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.
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.