VLOOKUP Hack #10: Maslow’s Hammer

Psychologist Abraham Maslow wrote a line that is often referred to as Maslow’s Hammer. It goes something like this: “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail.” I’ve heard the simplified version more often: “If all you have is a hammer, everything looks like a nail.”

The sentiment here is we tend to over rely on a familiar tool. Because VLOOKUP is such an incredible function, this can happen to Excel users. We tend to use VLOOKUP for everything. But, here’s the hack. There are other options that are often better suited to the task at hand.

So, in this, the final post in the VLOOKUP Hacks series, we’ll explore several alternatives to VLOOKUP.

Specifically, we’ll look at these:

• SUMIFS
• COUNTIFS
• INDEX/MATCH
• Get & Transform / Power Query

Let’s just take them one by one.

SUMIFS

SUMIFS is not typically thought of as a lookup function, but, it actually makes a great lookup function when the return value is numeric. Why would we use SUMIFS instead of VLOOKUP? There are a few reasons. The sort order doesn’t matter, multiple conditions are supported, the column order doesn’t matter, and it will match equivalent values even when the data types are different.

If you’d like to learn more, here are a few links:

• https://www.excel-university.com/vlookup-vs-index-match/
• https://www.excel-university.com/multi-column-lookup-with-vlookup-and-sumifs/
• https://www.excel-university.com/vlookup-on-two-or-more-criteria-columns/

COUNTIFS

Often, Excel users use VLOOKUP to perform list comparisons. It works well in many situations, especially when you want to return a related value. But, if you are simply trying to determine if one item appears on another list, and not return a related value, COUNTIFS is great because it supports multiple conditions.

If you’d like to learn more about this, here is a link to my CalCPA article that explains more about about it:

• https://www.excel-university.com/articles/cal-cpa/comparing-spreadsheet-lists-with-ease/

INDEX/MATCH

One underlying assumption in VLOOKUP is that the lookup column is the left-most (or first) column in the lookup range. When it isn’t Excel users often cut/paste to manually reposition it. This is a manual step that can be eliminated by using INDEX/MATCH instead. Check it out:

• https://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

Get & Transform / Power Query

Sometimes we use VLOOKUP to merge, or combine, two different tables. Using multiple tables is something that is totally integrated inside of Power Query / Get & Transform queries. Here are some links in case you’d like to learn more:

• https://www.excel-university.com/get-transform-an-alternative-to-vlookup-list-comparisons/
• https://www.excel-university.com/use-get-transform-to-reconcile-two-lists/

Conclusion

I hope you have enjoyed the VLOOKUP Hacks blog series as much as I have! It is a critical function for Excel users. If you have any other fun VLOOKUP tips or alternatives, please share by posting a comment below.