Are you looking for a way to find the last non-empty column in a row in Google Sheets? You can use the XLOOKUP function as the core function for this task. Depending on your need—whether you want the column number, address, or letter—you can combine it with COLUMN, CELL, or REGEXEXTRACT.
Here’s a quick breakdown:
- COLUMN + XLOOKUP to find the last non-empty column number
- CELL + XLOOKUP to find the last non-empty column address
- REGEXEXTRACT + CELL + XLOOKUP to find the last non-empty column letter
The formula is simple. Let’s look at some examples.
Find the Last Non-Empty Column Number in a Row
Let’s say you want to find the last non-blank column in row 3. Use the following formula in any other row:
=ArrayFormula(COLUMN(XLOOKUP("*?", 3:3&"", 3:3,,2, -1)))
How it works:
- The
3:3&""part converts all values in the row to text, allowing XLOOKUP to match both numbers and text. - The wildcard
*?in XLOOKUP ensures it matches any non-empty cell (whether it’s a number or text). - XLOOKUP searches from right to left (
-1) and returns the last non-empty cell in the row. - COLUMN then returns the column number of that cell.
Find the Last Non-Blank Column in the Same Row
What if you want the formula and result in the same row?
For example, to search from B3 to Z3 and return the last non-blank column number in cell A3:
=ArrayFormula(COLUMN(XLOOKUP("*?", B3:Z3&"", B3:Z3,,2, -1)))This is perfect when you’re working row by row.
Find the Cell Address of the Last Non-Empty Column in a Row
If you want the full cell address instead of just the column number, you can use CELL:
=ArrayFormula(CELL("address", XLOOKUP("*?", 3:3&"", 3:3,,2, -1)))
To apply it in the same row, say in A3 while checking B3:Z3:
=ArrayFormula(CELL("address", XLOOKUP("*?", B3:Z3&"", B3:Z3,,2, -1)))Find the Last Non-Empty Column Letter in a Row
To extract just the column letter of the last non-empty column in a row, wrap the CELL function in REGEXEXTRACT:
=ArrayFormula(REGEXEXTRACT(CELL("address", XLOOKUP("*?", B3:Z3&"", B3:Z3,,2, -1)), "[A-Z]+"))
This extracts only the letter portion from the address returned by CELL.
Related Resources
- Google Sheets: How to Return First Non-blank Value in a Row or Column
- VLOOKUP to Get the Last Non-blank Value in a Row in Google Sheets
- How to Dynamically Exclude Last Empty Rows and Columns in Google Sheets
- Count From First Non-Blank Cell to Last Non-Blank Cell in a Row in Google Sheets
- Get the Header of the Last Non-blank Cell in a Row in Google Sheets
- XMATCH First or Last Non-Blank Cell in Google Sheets
- Get the Headers of the First Non-blank Cell in Each Row in Google Sheets – Array Formula
- Find the Address of the Last Used Cell in Google Sheets
Hooray, I solved my problem. I used the ArrayFormula presented at the very beginning of this article to set up three cells in my worksheet to return column numbers instead of column letters. Then I used curly brackets in my query in order to reference these three cells:
1:
=ArrayFormula(IFNA(match(2,1/($A2:2"")))-2)2:
=ArrayFormula(IFNA(match(2,1/($A2:2"")))-1)3:
=ArrayFormula(IFNA(match(2,1/($A2:2""))))Formula:
=QUERY({'Multi-Course'!$A1:BJ}, "SELECT Col5 WHERE Col"&'Multi-Course'!$I1&"='Q' and Col"&'Multi-Course'!$J1&"='Q' and Col"&'Multi-Course'!$K1&"='Q' LABEL Col5 'Player'",1)Yours is the first place I look for Google Sheets solutions. Thank you for putting out this most helpful series of articles.
Hi, Roger Ederle,
I have read this comment after posting a reply to your earlier thread.
I hope the below tutorial will help other readers to learn.
Get the Last Column from a Data Range in Google Sheets.
How can I use this in a Query?
=QUERY($A4:BJ, "SELECT E WHERE T='Q' and U='Q' and V='Q' LABEL E 'Player'",0)Where T, U, and V are the last three columns that have data (the letter ‘Q’) with many more empty columns to the right of the last data column.
I add more data to the range A4:BJ every week, one column per week ending with column BJ in the last week of the year.
I always want to select E for the last three data columns. I would like to automate this.
Right now, I manually change the letters of the last three columns every week. With many queries for different data items, this becomes very tedious.
Thanks.
Hi, Roger Ederle,
You can try this.
=ArrayFormula(QUERY({Sheet1!A4:BR}, "SELECT Col5 WHERE "&"Col"&IFNA(match(2,1/(Sheet1!A5:5<>"")))-2&"='Q' and Col"&IFNA(match(2,1/(Sheet1!A5:5<>"")))-1&"='Q' and Col"&IFNA(match(2,1/(Sheet1!A5:5<>"")))&"='Q' label Col5'Player'",1))Assume your data is in Sheet1. Insert this formula in Sheet2.
I have used the same method to find the last non-empty column and implemented the same in the Query function WHERE clause.
For the last 3 non-empty columns, I have used the below logic.
Last_non_empty_column-2, Last_non_empty_column-1, and Last_non_empty_column.