There are a few lookup functions you can use in spreadsheets. HLOOKUP is one of the more familiar functions, but not many have heard about LOOKUP. It’s a rare function that’s really only useful in one particular task.
How to Insert a LOOKUP Function
Many spreadsheet users barely know about the LOOKUP function, and there’s a reason for that: it’s used rarely and is effective for less than a handful of purposes.
Okay, maybe just one.
If you have a list of values, a long list of values, that you need to keep track of or constantly update, you can use this function to do that. It does require some special tweaking.
Scenario: The list above changes from time-to-time, and I need to track the last value in the list. For this, I need to use the LOOKUP, but let’s take this step-by-step first.
Step 1: Testing the Cell
There are a few ways to test the cell, and I’ve decided to use the ISBLANK function. This says, if the cell is blank, return TRUE. Otherwise, return FALSE. with =ISBLANK(a:a) I test the entire column. You’ll understand why I do that in a later step. But to test all 300+ cells manually is impractical and defeats the purpose.
Step 2: Testing the Entire Array
How do we apply this formula to the entire column? We use wrap ISBLANK in ARRAYFORMULA.
ARRAYFORMULA is an advanced (and VERY cool) function that I’ll go over in another Intermediate Spreadsheet course in detail. Basically, it conducts multiple calculations on arrays. You could type the function name, but the fastest way to get the formula to propagate is to use Ctrl + Shift + Enter. This populates all the cells in the column.
But having TRUE/FALSE doesn’t really help. We need to convert those values to numbers somehow
Step 3: Turn TRUE/FALSE into numbers
One way to force those values to numbers is to convert them multiply them by a number. In this case, I chose to multiply them by 1, and here’s what happened:
By multiplying ISBLANK results by 1, the result will be either 0 or 1. Remember, FALSE represents 0 and TRUE, 1.
NOW, it’s time for LOOKUP!
Step 4: Integrate LOOKUP
LOOKUP takes 3 arguments:
- Search_key: This is the value you’re looking for. It can be exact or approximate.
- Array/Range: The array you want to search. It has to be a single row or a single column.
- Result Range: Array from which you’ll return the result.
LOOKUP is wrapped in ARRAYFORMULA, because it needs to test every single result in the column, and it needs to do so in the quickest, most efficient way possible.
The result is: =ARRAYFORMULA(LOOKUP(0.5, ISBLANK(A:A)*1,A:A))
The reason why we choose 0.5, is because when the formula tests the values, it will meet upon 1, which means that the cell is blank. Because 1 is greater than 0.5, it will drop back and choose the cell before it, which is 0.
The last cell contains 218, which is correct!
This formula is dynamic, which means that the minute you add something to the list, that formula automatically updates.
Here’s a live example for of how to build the LOOKUP function:
How to Use the HLOOKUP Function
The next function that not many people use is the HLOOKUP. HLOOKUP is the transposed version of VLOOKUP (which I cover in my online course); instead of looking through a column, you search a row.
Let’s take a look at the business office rental example, from my previous post.
We want to find the number of seats for Auditorium 2.
HLOOKUP takes four arguments: the value you are looking for, the range or array you’ll be searching through, the row index, and whether the row is sorted.
We want to pull the number of seats for Auditorium 2. For this, the function formula is
=HLOOKUP(F2, $A$1:$D$10, 7, False)
Notice that unlike the INDEX function, you need to include the headers in the range of the second parameter. Plus we counted the number of rows, so if we don’t include the headers, the answer would be off.
The result is 150:
which, according to the table is correct.
But, the index is hard-coded, and we need to make this more dynamic. MATCH is the perfect choice here. Let’s create the function first, and then transport it.
To match the Auditorium 2, we need to scan the values in the column, because this is an HLOOKUP that goes across and then goes down. We see that it brings back the right value for the index, which is 7, so we transplant this function into HLOOKUP:
It’s really that simple!
Nesting functions is fun, but it can be tricky. For example, there are some examples that create nested lookups. Typically, it’s VLOOKUP(HLOOKUP. This is one of the cases where, just because you can, doesn’t mean you should. This combination doesn’t make much sense, because you’ll have to either hard-code the index parameter in the HLOOKUP, or use a MATCH.
But you’re better off just using one or the other with the MATCH, because it’s less confusing. Additionally, in some cases, nesting functions makes sheets heavier, and if you have a sheet full of nested functions, it’s hard to load, especially if you’re using MS Excel.
Simpler is not only more elegant, but lighter, better, and more powerful.
Let me know if you agree or not.