Who doesn’t love the Vlookup function?
It’s probably the most famous “advanced” excel & spreadsheet function and rightly so, because you can do amazing things with it.
This email contains a Vlookup formula challenge walkthrough. Something fun to augment the skills you’re learning in the videos.
Let’s see how we can use array formulas to build “virtual” tables nested inside a Vlookup.
VLOOKUP Formula Problem to solve.
We have a data table and we want to search for “Bob Davis” using Vlookup and return the amount associated with him.
The problem is that in the data table, names are split across two columns, First Name and Second Name. So a standard Vlookup isn’t possible at the moment.
VLOOKUP Formula Solution:
There’s an easy solution: create a quick helper column to combine the first and last names into a full name and use this as the search column.
But what about doing it without creating a new column in the data table?
The trick is to create a new table dynamically, which has the helper column. Let’s see it in three steps:
Step 1: Create the full name column
First we need to generate the array of full names using this formula:
=ArrayFormula(A2:A9&” “&B2:B9)
The ” ” adds a space between the first and last names. The output of this single formula is an array of full names:
(This is an Array formula. You enter the formula and then hit Ctrl + Shift + Enter, or Cmd + Shift + Enter (Mac) to add the ArrayFormula designation.)
Step 2: Add the other columns from the original table
In this step we build the new search table by adding the other columns from the original table that we want to search, using this formula:
=ArrayFormula( { A2:A9&” “&B2:B9 , C2:D9 } )
The curly braces { … } combine arrays. Using a comma (,) between curly brace arrays treats them as columns next to each other, which is what we want, as you can see in the image:
This array formula combines columns A and B into a full name column and then adds back columns C and D to create a new table in H2:J9.
Step 3: Perform the Vlookup
Now we have created the new table, we simply nest it as the range input in a standard Vlookup, with this formula:
=ArrayFormula( VLOOKUP( G2 , { A2:A9&” “&B2:B9 , C2:D9 } , 3 , false ) )
which gives the desired output of $383:
Note: if you want to use this Vlookup on another row to look up another full name, you’ll want to lock those range references to avoid getting errors.
This concept of creating “virtual” tables that you nest inside of other formulas is super useful. You can also use the Filter function, the Query function etc. to create nested tables.
Similarly, we are going to learn about LEFT VLOOKUP formula to look up the value in the left of the range references. It’s really fun and solve unique problems while you work with the data.
You must know the basics of VLOOKUP Formula.
The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.
The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the VLOOKUP function can be entered as part of a formula in a cell of a worksheet.
Regular Syntax of VLOOKUP Formula
In Excel: VLOOKUP( value, table, index_number, [approximate_match] ) In Google Sheet: VLOOKUP(search_key, range, index, [is_sorted]) Both are same except the naming. Example VLOOKUP(10003, A2:B26, 2, FALSE) Summary Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found. search_key The value to search for. For example, 42, "Cats", or I24. range The range to consider for the search. The first column in the range is searched for the key specified in search_key. index The column index of the value to be returned, where the first column in range is numbered 1. is_sorted - [optional] Indicates whether the column to be searched (the first column of the specified range) is sorted, in which case the closest match for search_key will be returned.
