You might have an Excel situation where you need to find the last non-empty cell value whether in a Column or in a Row. Things can get more difficult if you lookup the last value based upon a condition.
In this article, I will show you 6 methods to lookup the last non-empty cell value from Simple to Mind Blowing methods. Whether you have access to Dynamic Array functions or not… This tutorial has you covered.
Let me know in a comment, which method you prefer?
So, let’s dive in
Scroll down to Download the Start file and practice.
Method #1
The LOOKUP function
Arguments:
=LOOKUP(lookup_value,lookup_vector,result_vector)
The Work Situation:
I have a list consisting of five columns: “Full Name”, “City”, “State” and “Balance”. I want to create a function in cell G1, that extracts the last record, even if I add or remove records to the list.
I want to start talking about the lookup_Vector ( the second argument of the LOOKUP function.
What if I create a simple formula in cell G1 that evaluates Column A to see if it is Not Blank:
=A:A<>””
I get an entire column of TRUE (which means Not Blank) and FALSE (which means Blank)
I get an entire column of TRUE (which means Not Blank) and FALSE (which means Blank)
=1/(A:A<>””)
So, I get an entire column of 1 (which means Not Blank) and #DIV/0! (which means Blank)
My goal is to extract the last occurrence of 1 which is the last record. So, I wrap the above function inside a LOOKUP function. Since the LOOKUP function returns an approximate match, then, if my lookup value is 1 or >1, the LOOKUP function will keep searching in the entire column until it bumps into the #DIV/0! Error so it moves backwards to the LAST occurrence of 1 and returns the corresponding value from the return array.
My “Return Array” (3rd. argument of the Lookup function) will be from Column A, and the function will look like this:
=LOOKUP(2,1/(A:A<>””),A:A)
Notes:
I used 2 for the lookup value and it can be replaced by any number>=1 as I explained.
It’s not good practice to select entire columns as it reduces the performance and efficiency of calculations. You can rather use a range that is way bigger than the expected number of records, such as:
=LOOKUP(2,1/(A1:A500<>””),A1:A500)
In cell G1 I get the Full Name of the LAST record. I can click and drag to copy the function to cell J1
Now let’s add a few more records to the end of the list (Drag the records from H27:K35). Automatically the new LAST record is extracted
Method #2
The OFFSET function.
Arguments:
=OFFSET(reference,rows,cols,[height],[width])
The Work Situation:
Same as previous
You can download the exercise file and follow along by clicking here.
What is the OFFSET function?
The OFFSET function Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
The OFFSET function syntax has the following arguments:
◾ Reference Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
◾ Rows Required. The number of rows, up or down, that you want the upper-left cell to refer to. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
◾ Cols Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
◾ Height Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
◾ Width Optional. The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
In this example I use cell A1 as a starting point for the OFFSET function. I then want to move down a certain number of Rows equal to the number of non-blank cells in column A. I do not want to move any Columns right or left, and therefore the third argument of the function will be 0. The function will read:
=OFFSET(A1,COUNTA(A1:A50)-1,0)
Copy the function from G1 to J1 to extract the entire last record.
Note:
The OFFSET function is a volatile function that recalculates every time you make a change to the worksheet. Many users prefer to avoid it.
Method #3
The INDEX function.
Arguments:
=INDEX(array,row_num,column_num)
The Work Situation:
In this example, I want to extract in cell F4, the last Sales Amount (Column B) of a specific product. So, not only do I want the last amount, but I want the last Sales amount matching the Product I select with a drop Down list in cell F1. Conditional Formatting highlights all records matching the selected product from the drop list.
Remember 3 things:
- I want a return value from B2 to B14
- My condition is from the drop list in cell F1 (currently Product 2)
- I want to find the LAST occurrence of the condition in A2:A14
To build the logic in cell F4 I will compare F1 to each cell in the range A2:A14 by typing:
=A2:A14=$F$1
I get a bunch of True (Product 2) and FALSE (any other Product).
I want to convert the TRUE ► 1 and FALSE ► #DIV/0! So, I divide 1 by the previous formula, as follows:
If I put the above function in a MATCH function as a lookup array, with a lookup value of 2 (which is >1) the MATCH function by default returns an approximate match:
=MATCH(lookup_value,lookup_array,match_type)
=MATCH(2,1/(A2:A14=$F$1))
My goal is to extract the last occurrence of 1 which is the last record for ”Product 2”. Then I wrap the above function inside a MATCH function. Since the MATCH function returns an approximate match, then, if my lookup value is >=1, the MATCH function will keep searching in the entire range until it bumps into the #DIV/0! Error so it moves backwards to the LAST occurrence of 1 and returns the corresponding row number. Which is the position of the last occurrence of “Product 2” in the list: “11”. This will provide a “Row number” to the INDEX function (start counting from Row #2)
Now we can wrap the MATCH function inside an INDEX function as follows:
=INDEX(B2:B14,MATCH(2,1/(A2:A14=$F$1)))
And we get the last sales amount for “Product 2”
Test by selecting a different Product in cell F1
All the functions used in the previous 3 methods are available in all versions of Excel. However, if you have Microsoft 365, you can use Dynamic Array functions, as in method #4
You can download the exercise file and follow along by clicking Here
Method #4
The XLOOKUP function. Available in Microsoft 365
Arguments:
=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)
The Work Situation:
Same as previous
The XLOOKUP has 6 arguments, and the secret is in the last argument:
- Lookup Value ► F1
- Lookup Array ► the range A2:A14
- Return Array ► the range B2:B14
- If not Found ► I will skip it
- Match Mode ► 0 for exact
- Search Mode ►I want to search in reverse order (from bottom up) ► so I select -1
And the function will be:
We get the LAST sales amount for “Product 5”. Test with a different product in cell F1.
That was the basic use of the XLOOKUP function.
Now we want to take it to the next level.
Method #5
The XLOOKUP function. Available in Microsoft 365 and I want to use it in a more robust way.
Arguments:
=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)
The Work Situation:
Same as previous
We have a list of 5 columns: “Item”, Buyer”, “Cost”, “QTY”, “Total” and multiple rows. There are blank rows in between and blank cells as well.
I will use the XLOOKUP function to extract the LAST non-empty cell value in column A whether it is Text or Number and in row # 7 whether it is Text or Number.
I start by creating my function in cell H2, searching for the LAST value in Column A, whatever this value. So, I use a Wildcard “*” asterisk, which means anything of any size. The function reads:
=XLOOKUP(“*”,A1:A50,A1:A50,””,2,-1)
- Lookup Value ► “*” wildcard
- Lookup Array ► I selected a range much bigger than the data: A1:A50
- Return Array ► I selected same previous range
- If not Found ► “” which means “nothing”
- Match Mode ► 2 for Wildcard character match
- Search Mode ► -1 for Search last to first
This function returns the LAST non-empty cell value in column A
If in Cell A19 I type =”” (empty string) ► H2 appears Blank
I want to exclude any empty string and make sure that the lookup value has at least one single character, so I use a second wildcard “?”. My function will look like this:
=XLOOKUP(“*?*”,A1:A50,A1:A50,””,2,-1)
And it will return the Last non-empty cell value
Although we solved the problem of empty string, we still have another problem if we type any number in column A ► It will not be perceived by the current XLOOKUP function. In fact the wildcard searches for any character, not for digits ☹
To solve this problem, I will wrap the lookup array in another function that converts the entire array to text. This function is VALUETOTEXT.
My function will read:
=XLOOKUP(“*?*”,VALUETOTEXT(A1:A50),A1:A50,””,2,-1)
Note:
I do not need to wrap the return array in a VALUETOTEXT function so that digits are right-aligned and perceived as numbers.
And we solved all the problems 😊
Lookup By Row:
I can use the same previous function in Cell H1 to lookup for the Last non- empty cell value in a row such as Row #7
The function will read:
=XLOOKUP(“*?*”,VALUETOTEXT(7:7),7:7,””,2,-1)
We were able to extract the Last non-empty cell value in a Column or in Row, whether it’s text or number by using this powerful combination of functions and wildcards.
Method #6
This is the most stunning technique in which I use a simple trick in Power Query to resolve a complex work situation.
Subscribe to my YouTube Channel to be notified as soon as I post the tutorial.
YouTube.com/OfficeInstructor
You can watch the tutorial on YouTube by clicking the link beloq:
https://www.youtube.com/watch?v=lw0kSLSYusM
Remember, The Best Is Yet To Come.
