The lookup(), vlookup(), and hlookup() functions (aka xLookup ) are described in many tutorials and videos. Rather than adding yet another explanation of the basics of these powerful functions, I’m going to explore a better, more more efficient way of using these functions with a few simple examples. Stay with me and you will quickly master this new technique.
My intent is to share what I learned about a less common yet beneficial way to leverage these functions to improve some of the most common tasks. Many people with a basic technical background know that a lookup function is just an implementation of a hash table.
What’s a Hash Table?
A Hash Table is an associative structure that can map keys to values. A simple example is a structure that maps the 2 letter symbol of each American state to the full name of the state. A lookup function, in a way similar to a hash table, will allow my spreadsheet, given 2 letters identifying a state, like “CA”, to retrieve its full name “California”. The Google implementation of the lookup functions is easy to understand and to use. Let’s see an example.
A simple lookup() example
Let’s look to an example where the cell A1 contains the key we need to resolve, and the value of cell B1 is defined by the following formula:
= lookup(A1,A3:B7)
First important note: the lookup() formula works correctly only if the keys are sorted. This is not obvious from the name of the function and is a source of frustration among almost all-new Google Sheet users. It’s very common to find user complaining that the Google sheet lookup is not working, simply because they didn’t sort the keys.
Are you worry about wasting cells for the lookup-map
To create a map between keys and values, we always need to allocate a range of cells in our spreadsheet to host all the keys and the matching values. There are plenty of times where it makes sense to display the map, but there are also times where we don’t really need to give the map all that visibility. Sometimes using cells to create a map in our spreadsheet gets in our way. The good news is that there is a way to hide the map directly inside the formula.
How to create a lookup-map without using any cell
How to create a map without cells? By using a syntax that Google Sheet calls Literal Arrays. The concept is simple: instead of referencing a range of cells containing the map, we can use a simple syntax to describe the map as a parameter of the function. The literal array of a map is a bi-dimensional array of values composed of two columns and a few rows (as in our first example). We use brackets to mark the beginning and the end of the array, semi-columns to separate rows, and commas to separate cells inside a row. The simple map we used in our example above can be express as literal array:
{
"AL", "Alabama";
"AZ", "Arizona";
"AK", "Arkansas";
"CA", "California";
"CO", "Colorado"
}
Therefore the previous formula can expressed, without referring to an eternal map, as follow:
= lookup(A1, {
"AL", "Alabama";
"AZ", "Arizona";
"AK", "Arkansas";
"CA", "California";
"CO", "Colorado"
})
The only reference to a cell is the address A1 of the cell containing the key we want to resolve. If we don’t like to worry about working only with sorted keys, we can use the vlookup() function instead of the lookup().
= vlookup(A1, {
"AL", "Alabama";
"AZ", "Arizona";
"AK", "Arkansas";
"CA", "California";
"CO", "Colorado"
}, 2)
This is the function inside a Google Spreadsheet:
No more need to use valuable real estate space inside your spreadsheet to describe a map.
I hope this simple explanation will help you to create better and more efficient spreadsheets.
Reusing the lookup-map in a Google Sheet
If you need to use your lookup-map in multiple formulas, your map might deserve a more prominent position, maybe in a separate spreadsheet tab adequately named. If you intentionally want to hide the map from the users of your spreadsheet, there is another option. Create a simple Javascript function that returns that map and call that function in every lookup() you need. You don’t have to worry about having to type the map over and hover again.
A better and more technical solution: create a Javascript lookup-map
Don’t worry, it’s not complicated.
First step, you need to open the Script Editor from the “Tools” Menu.
Once you are in the editor, type the following Javascript code:
// Return a lookup-map organized as
// [[key, value], ..., [key, value]]
function usStatesVMap() {
return [
["AL", "Alabama"],
["AZ", "Arizona"],
["AK", "Arkansas"],
["CA", "California"],
["CO", "Colorado"]
];
}
In the snippet above, we describe the same array I was describing using the Literal Array syntax, but this time I’m using the Javascript notation and syntax. Once I’ve created the usStatesVMap() function, I only need to save it using the [SAVE] icon on the [File][Save] menu entry.
Now I can go back to my spreadsheet and update my formula:
= vlookup(A1, usStatesVMap(), 2)
Definitively a cleaner formula than our previous version. Easier to understand and to edit.
FYI: the map still accessible to all the users of your spreadsheet as long as they have the knowledge to explore the “Script” section and to edit Javascript code.
Let’s make a lookup-map for the hlookup() function
The two major lookup function vlookup() and hlookup(0 are equivalent. When connecting these functions to a lookup-map generated by Javascript code, the only difference is how the data is organized in the array. While the vlookup() function expect the map to be organized in pairs (key, value), the hlookup() function expect the map to be organized in two arrays, one with the keys and one with the values in the matching positions.
Let’s write a simple lookup-map in Javascript. Go back to the script editor, and type the following Javascript code:
// Return a lookup-map organized as
// [[key, key, ..., key], [value, value, ..., value]]
function usStatesHMap() {
return [
["AL", "AZ", "AK", "CA", "CO"],
["Alabama", "Arizona", "Arkansas", "California", "Colorado"]
];
}
In the snippet above, the usStatesHMap() functionreturns a similar array but arranged in different way to accomodate the requirements of hlookup(). Once I’ve created the usStatesHMap() function, I save and I’ll go back to the spreadsheet to update my formula.
= hlookup(A1, usStatesHMap(), 2)
When deciding between vlookup() and hlookup(), because there are no significant differences, just use the function that works better for you.
Conclusions
I hope this simple technique can help you create more robust and manageable spreadsheets. If you find this article useful or you have any recommendations on improving this solution, please let me know by leaving a comment in the area below.
Similar Articles
Connecting Google Maps and Google Sheets to Solve the Traveling-Salesman Problem
Use Google Sheets and and Google Maps to find the shortest route among a list of addresses. Learn how to use the power of Google Maps from Google Sheets. It’ss not that complicated.
Referring Columns By Name in Google Sheets Query()
This article shows a better way to import and link CSV files into a Google spreadsheet. By using a simple technique you can protect your spreadsheet and safely query data from an external CSV even when the structure of the CSV files changes.
Franco Folini 