Explanation
In this example, the goal is to get the last value in column B, even when data may contain empty cells. A secondary goal is to get the corresponding value in column C. This is useful for analyzing datasets where the most recent or last entry is significant. In the current version of Excel, a good way to solve this problem is with the XLOOKUP function. In older versions of Excel, you can use the LOOKUP function. Both methods are explained below.
XLOOKUP function
The XLOOKUP function is a modern upgrade to the VLOOKUP function. XLOOKUP is very flexible and can handle many different lookup scenarios. The key feature, in this case, is the ability to perform a “last to first” search. The generic syntax for XLOOKUP looks like this:
=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)
Where each argument has the following meaning:
- lookup_value - the value to look for
- lookup_array - the range or array to search within
- return_array - the range or array to return values from
- if_not_found - value to return if no match is found
- match_mode - settings for exact, approximate, and wildcard matching
- search_mode - settings for first to last, last to first, and binary searches
For more details, see How to use the XLOOKUP function .
In this example, we want to find the last non-empty cell in a range, so we use XLOOKUP like this:
=XLOOKUP(TRUE,B5:B16<>"",B5:B16,,,-1)
At a high level, we configure XLOOKUP to look for TRUE in a lookup array created with a logical expression, and we enable a “last to first” search by providing -1 for search_mode :
- lookup_value - TRUE
- lookup_array - B5:B16<>""
- return_array - B5:B16
- if_not_found - omitted, defaults to #N/A
- match_mode - omitted, defaults to exact match
- search_mode - given as -1 for search last to first
The main trick is the logical expression used for lookup_array :
B5:B16<>""
The <> operator means “not”, so <>"" means “not empty”. Because B5:B16 contains 12 values, the expression returns an array that contains 12 TRUE and FALSE results.
{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
In this array, the TRUE values in the array indicate non-blank cells and the FALSE values indicate blank cells. This array is delivered directly to the XLOOKUP function as the lookup_array :
=XLOOKUP(TRUE,{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},B5:B16,,,-1)
Because search_mode is -1, XLOOKUP starts its search from the end of the array and matches the first TRUE encountered (the second to last value in the array). With the return_array provided as B5:B16, XLOOKUP returns 15-Jun-23 as a final result.
Corresponding value
The XLOOKUP formula in cell F7 to get the corresponding price from column C is almost identical:
=XLOOKUP(TRUE,B5:B16<>"",C5:C16,,,-1)
Note the only difference is the return_array, which is provided as C5:C16.
Dealing with errors
If the last non-empty cell contains an error, the error will be ignored. If you want to return an error that appears last in a range you can adjust the formula to use the ISBLANK and NOT functions like this:
=XLOOKUP(TRUE,NOT(ISBLANK(B5:B16)),B5:B16,,,-1)
This version of the formula will show an error if the last non-empty cell contains an error.
Last numeric value
To get the last numeric value, you can use the ISNUMBER function like this:
=XLOOKUP(TRUE,ISNUMBER(B5:B16),B5:B16,,,-1)
Last non-blank, non-zero value
To check that the last value is not blank and not zero, you use Boolean logic like this:
=XLOOKUP(1,(B5:B16<>"")*(B5:B16<>0),B5:B16,,,-1)
Notice the lookup value is now 1 instead of TRUE. For more details, see Boolean Algebra in Excel and XLOOKUP with multiple criteria .
Older versions of Excel
In older versions of Excel, there is no XLOOKUP function, so we need another approach. One solution is to use the LOOKUP function , which can handle array operations natively. The generic formula looks like this:
=LOOKUP(2,1/(range<>""),range)
Adjusting references for this problem, we have the following formula:
=LOOKUP(2,1/(B5:B16<>""),B5:B16)
Note: This is an array formula . But because LOOKUP can handle the array operation natively, the formula does not need to be entered with Control + Shift + Enter, even in older versions of Excel.
Working from the inside out, we use a logical expression to test for empty cells in B5:B16:
B5:B16<>""
The logical operator <> means not equal to , and "" means empty string , so this expression means B5:B16 is not empty . The result is an array of TRUE and FALSE values, where TRUE represents cells that are not empty and FALSE represents cells that are empty . Because B5:C16 contains 12 values, the expression returns an array with 12 results:
{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
Next, we divide the number 1 by the array. The math operation automatically coerces TRUE to 1 and FALSE to 0, so we have:
1/({1;1;1;1;0;1;1;0;1;0;1;0})
Since dividing by zero generates an error, the result is an array composed of 1s and #DIV/0 errors:
{1;1;1;1;#DIV/0!;1;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!}
Here, the 1s represent non-empty cells, and errors represent empty cells. This array becomes the lookup_array argument in LOOKUP. The lookup_value is given as the number 2. This may seem baffling, but there is a good reason. We are using 2 as a lookup value to force LOOKUP to scan to the end of the data . LOOKUP automatically ignores errors, so LOOKUP will scan through the 1s looking for a 2 that will never be found. When it reaches the end of the array, it will “step back” to the last 1, which corresponds to the last non-empty cell. Since the result_vector is B5:B16, the final result is: 15-Jun-23.
Note: the key to understanding this formula is to recognize that the lookup_value of 2 is deliberately larger than any values that will appear in the lookup_vector. When lookup_value can’t be found, LOOKUP will match the next smallest value that is not an error: the last 1 in the array. This works because LOOKUP assumes that values in lookup_vector are sorted in ascending order and will always perform an approximate match. When LOOKUP can’t find a match, it will match the next smallest value.
Get corresponding value
You can easily adapt the lookup formula to return a corresponding value. For example, to get the price associated with the last value in column B, the formula in F7 is:
=LOOKUP(2,1/(B5:B16<>""),C5:C16) // get price
The only difference is that the result_vector argument has been supplied as C5:C16.
Dealing with errors
If the last non-empty cell contains an error, the error will be ignored. If you want to return an error that appears last in a range you can adjust the formula to use the ISBLANK and NOT functions like this:
=LOOKUP(2,1/(NOT(ISBLANK(B5:B16))),B5:B16)
This version of the formula will show an error if the last non-empty cell contains an error.
Last numeric value
To get the last numeric value , you can use the ISNUMBER function like this:
=LOOKUP(2,1/(ISNUMBER(B5:B16)),B5:B16)
Last non-blank, non-zero value
To check that the last value is not blank and not zero, you can use Boolean logic like this:
=LOOKUP(2,1/((B5:B16<>"")*(B5:B16<>0)),B5:B16)
For a more detailed explanation, see Boolean Algebra in Excel .
Explanation
This example is based on a fitness assessment where the goal is to award points based on how much weight was lifted at a given age. The solution described below is based on an INDEX and MATCH formula , but there are several tricky elements that must be considered, making this problem much more difficult than your average lookup problem. The formula in L12 is:
=INDEX(points,MATCH(L6,INDEX(data,0,MATCH(L5,--LEFT(age,2),1)),-1))
The formulas in L8:L10 are for additional explanation only. They show how the problem can be broken down into intermediate steps.
Data layout
The first step is to verify the structure of the data and the behavior that will be needed to solve this problem. In column B, the range B5:B25 contains points. The formula will ultimately need to return a number from this range as the final result. In row 4, the range C4:I4 contains age ranges. Finally, the range C5:I25 contains lift data. There are three named ranges in the worksheet: points (B5:B25), age (C4:I4), and data (C5:I25). These named ranges are for convenience only, to make the formula easier to read and write.
Behavior
When a user enters an age in L5 and a lift in L6, the formula should calculate points based on these two inputs. This means the formula needs to match the correct age column, locate the best match in the lift data, then traverse back to column B to get a final result. At a high level, there are three steps:
- look up the age column
- Look up lift
- Retrieve points
The screen below shows an overview:
Note: the formulas in L8:L10 are just to make it easier to understand how the formula works, they are not used in the final formula in L12.
Complications
This problem is notable because the configuration is “backwards” from what is usually expected. Instead of matching an outer row and column, and retrieving a value from inside the table, we need to match a value inside the table and traverse back to an outer column (points). Also note that both the age lookup and the lift lookup are approximate match lookups, and the lift data appears in descending order. Finally, the ages in row 4 are text strings, whereas the age in L5 is numeric.
Age column
To get the correct age column we can use the MATCH function together with the LEFT function like this:
=MATCH(L5,--LEFT(age,2),1)
Working from the inside out, the first step is to identify the correct column number by matching the age in L5 against the age ranges in C4:I4. This is more difficult than usual, because while the age in L5 is a number, the age ranges in C4:I4 are text strings . Looking at the age ranges, the first thing to note is that we only need the first number in each age range to get the right match:
'
We can extract just the first number of each age range with the LEFT function like this:
--LEFT(age,2)
Because we are giving MATCH a range that contains 7 values, we get back 7 results in an array like this:
{"17","22","27","32","37","42","47"}
The double negative (–) converts these text values to actual numbers, resulting in an array of numeric values:
{17,22,27,32,37,42,47}
We now have what we need to get the column number with the MATCH function. The array above as lookup_array , and with age in L5, we have:
=MATCH(L5,{17,22,27,32,37,42,47},1)
We use 1 for match_type , because we want to find the largest value that is less than or equal to age. Simplifying, we have:
=MATCH(28,{17,22,27,32,37,42,47},1) // returns 3
The MATCH function returns 3 since 27 is the correct match, and the third item in the list.
The lift
Now that we know which age column we should use to look up the lift, we can move on to that task. This too is complicated. We know the column number is 3, and we know the lift data is in data (C5:I25), but how do we look up a lift of 295 with that information? The trick is to extract just column 3 (age range 27-31) before we look up the lift. We can do that with the INDEX function like this:
=INDEX(data,0,3) // returns column 3
By providing zero for row_num and 3 for column_num , INDEX will return the entire third column of data (C5:I25), which is the range E5:E25. So, putting it all together, we have:
INDEX(data,0,MATCH(L5,--LEFT(age,2),1)) // returns E5:E25
The MATCH function returns 3 to the INDEX function as column_num , and INDEX returns column 3 of data . We now have what we need to finally look up the lift. To do this, we embed the code above into another MATCH function:
MATCH(L6,INDEX(data,0,MATCH(L5,--LEFT(age,2),1)),-1)
Note the lookup_value is the lift from cell L6 (295) and match_type is -1, since the lift data appears in descending order and we want to find the smallest value that is greater than or equal to the lift. The lookup_array is created with the code explained above. Simplifying, we have:
MATCH(295,E5:E25,-1) // returns 12
MATCH returns 12 because 300 is the smallest value that is greater than or equal to 295, the position of 300 is in row 12 of E5:E25. Just to recap, all of the code below simply returns the number 12:
MATCH(L6,INDEX(data,0,MATCH(L5,--LEFT(age,2),1)),-1) // returns 12
We now have everything we need to solve the problem. We know the correct lift is in row 12 and column 3 of data, so we just need to traverse back across row 12 to retrieve the corresponding value from points (B5:B25). How can we do that? This is actually the easiest step in the problem. All we need to do is give the INDEX function the points range with a row number:
=INDEX(points,12) // returns 89
Recap
Putting everything together, the final formula in L12 is:
=INDEX(points,MATCH(L6,INDEX(data,0,MATCH(L5,--LEFT(age,2),1)),-1))
The inner MATCH function returns 3 to the inner INDEX as column_num :
=INDEX(points,MATCH(L6,INDEX(data,0,3),-1))
With 0 for row_num , INDEX returns the range E5:E25 to the outer MATCH as lookup_array :
=INDEX(points,MATCH(L6,E5:E25,-1))
The outer MATCH returns 12 as row_num to INDEX :
=INDEX(points,12)
Finally, the INDEX function returns 89, the points awarded for a lift of 295 at age 28.
