VLOOKUP for beginners
Hannah Sharron, editor of Spreadsheeto, talks us through one of Excel’s most powerful functions: VLOOKUP.
One of the most storied functions in Microsoft Excel is the VLOOKUP. It is a definitely a strange looking name to newcomers. However, once you learn what the VLOOKUP is capable (and not capable) of, it holds the power to provide efficiency and utility to a variety of spreadsheet challenges.
What does VLOOKUP stand for?
The name of the function itself simply means it is a vertical lookup. In contrast, the HLOOKUP function is a horizontal lookup.
So, what exactly is a vertical lookup? The function takes a lookup value, queries the leftmost column of a selected range for that value, then finds a data match to the right for the number of columns indicated in the formula.
VLOOKUP creates efficiency and provides flexibility
A simple demonstration of how to leverage the power of VLOOKUP is by creating a lookup for account titles based on the account number. In the following figure, note that we have a simple table showing ledger account numbers in the first column, and their description in the second column.
Now, let’s imagine we want to create a way to quickly lookup any of those account numbers and get the description on demand. We can create a convenient drop-down from the list of account numbers in one cell, and then in the cell next to that dropdown, we can place our VLOOKUP that returns the description based on the selected account number.
In another example, maybe we have a database that includes customer names and addresses. But perhaps there is some incomplete information and the only way we can get the information we need is to look it up from a different dataset.
In the following figure, we have a customer database that has no ‘county’ information (column G).
But we have located a database that contains a list of towns and the matching counties.
We simply need to join the two sets of data using a VLOOKUP and our customer database spreadsheet will be easy to complete.
Notice that by using the ‘city’ value (column E) in the customer table for our lookup value, the VLOOKUP matches the county from the ‘TownToCountyTable’ range shown in figure 4.
Conclusion
These are just a couple of examples of how powerful the VLOOKUP is. It is a high leverage skill to have in your repertoire, so be sure to always carry this in your “Excel toolbox”!
You might also be interested in
Replies (3)
Please login or register to join the discussion.
VLOOKUP and HLOOKUP are undoubtedly excelent tools in our box. However once you've tried INDEX along with MATCH you will probably not use VLOOKUP again. You eluded to what it is not capable of without giving any examples. As you say VLOOKUP searches the number of columns to the right of the leftmost column of the range. How annoying when you really want a column that happens to be to the left. With INDEX combined with MATCH problem solved. Please do check it out.
Dave
Despite what the vast majority Excel users learn, a VLOOKUP function can actually be directed to look to the left. Yes, really!! :-)
A formula using the following syntax will perform a right to left lookup - even on non-adjacent columns!
=VLOOKUP( Lookup_value, CHOOSE({1,2}, Lookup_range, Return_value_range), 2, FALSE )
Using CHOOSE to feed VLOOKUP in this way also means that only the target cells in the two relevant columns are referenced rather than cells in all columns in between - just like an INDEX/MATCH combo!
Note that the curly brackets in the first argument of the CHOOSE function {1,2} does NOT make this formula an array (CSE) formula, as CHOOSE can accept arrays.
The column_index_num is hard-coded as"2" because there are only ever 2 columns, being the Lookup_range and the Return_value_range.
The last argument for the VLOOKUP function (i.e. [range_lookup]) can be either True or False depending on your need.
The oft-quoted deficiencies of VLOOKUP are grossly overstated - mostly done when comparing a naked VLOOKUP (i.e. no other functions nested within it) with combination formulas such as INDEX/MATCH or INDEX/MATCH/MATCH. As demonstrated above, other Excel functions can be used to return the arguments of VLOOKUP, including CHOOSE, MATCH, COLUMN, and COLUMNS. All that is required is a good understanding of how Excel's functions work and what result they return - and a bit of lateral thinking.
Thanks - definitely a clever use of the lookup function. I have often rearranged, or duplicated columns to obtain a “right” hand side value and the V/H Lookup function has served me well for many years. Having found the INDEX/MATCH combo it’s now my method of choice; but will add “CHOOSE” to my options.
Best regards