A better use of lookup(), vlookup(), and hlookup() with examples for Google Sheets

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)
Google Sheets: Basic example on how to usa the lookup() function

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:

Google Sheet vlookup() function can be implemented with a Literal Array, hiding the hash table data from the 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.

The Google Sheet menu to access the Script Editor. The Script Editor is where we can write simple Javascript functions callable from the spreadsheet.

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