Advanced use of LOOKUP(), VLOOKUP(), and HLOOKUP() functions for Google Sheets

The regular use of lookup(), vlookup(), and hlookup() functions are described in so many tutorials and videos that It’s not my goal to add yet another explanation of these powerful functions.

My intent is to share what I learned about a less common yet beneficial way to leverage these functions to improve your most common tasks. People with a more technical background know that a lookup function is simply an implementation of a hash table.

What’s an 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, give the 2 letter 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.

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 of a lookup(0 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.

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 what 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 simple bi-dimensional array of values composed of two columns and a few rows (as in our first example). 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 as:

=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 want to remove the constraint about sorted keys we can use the vlookup() function instead.

=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 implemented with a Literal Array.

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.

Use a lookup map multiple times in a Google Sheet

If you need to use your map in multiple formulas, your map likely deserves a prominent position, maybe in a separate tab adequately named. If you intentionally want to hide the map to the users of your spreadsheet, there is another option. You can create a simple Javascript function that returns that map and use that function in every lookup() you need without having to type the map over and hove again.

A 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.

Once you are in the editor, type the following Javascript code:

function myMap() {
   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 myMap() 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,myMap(),2)

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 interact with Javascript code.

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