The XLOOKUP and its match mode 1 is one of the main parts of the formula that fills blank cells with the text immediately below it in Google Sheets.
The formula has the following peculiarities.
- It’s an array formula that sits in the topmost cell and spills down.
- It will cover any new (future) values at the bottom or top of the range.
- The formula generates the results, not in the source column, but in a new column range.
- It works in a numeric, date, text, or mixed-type date column. I’ve just used the term ‘text’ for clarity in the title.
Why should one want to fill blank cells with texts from the cells below?
If a value repeats in a column, we may leave it blank to make the data entry faster.
For example, we entered the name of two employees, “Ben” in cell A2 and “Lisa” in cell A11.
If the range is A2:A20, the blank cells are A3:A10 and A12:A20.
To fill A3:A10 with “Ben” and A12:A20 with “Lisa,” we can use my formula that fills blank cells with the values above.
If we enter “Ben” in A10 and “Lisa” in A20, the blank cells are A2:A9 and A11:A19.
Here we may fill these cells with the name “Ben” and “Lisa,” respectively.
In this case, we may require to fill blank cells with the texts from below.
Let’s go to the formula and then the explanation.
Fill Blank Cells with the Text, Number, or Date Immediately Below Them
Cell D1 in the above example contains the following formula.
=ArrayFormula(xlookup(row(A1:A),row(A1:A)/if(A1:A<>"",1,0),A1:A,"",1,1))Let’s test it by adding values at the bottom and top of the range.
Insert a cell below A1 and leave it blank or enter any value. The above formula will consider that cell while filling texts from the bottom.
You can add new values after A20. It will consider that also.
So we can call the formula, which fills blank cells with the text from the bottom of the cell, dynamic.
How does it work?
Formula Explanation
I’ve used an XLOOKUP array formula.
Syntax: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Let’s extract each parameter from the XLOOKUP formula, and here are them.
search_key: row(A1:A)
Note:- It requires array support. So we have wrapped the XLOOKUP with the ArrayFormula function.
The search key is the row numbers from 1 to n (please scroll down and see the screenshot).
lookup_range: row(A1:A)/if(A1:A<>"",1,0)
Here also, the above note is applicable!
It returns row numbers corresponding to the non-blank cells in column A and #DIV/0! in all blank cells (please scroll down and see the screenshot).
result_range: A1:A
missing_value: ""
match_mode: 1
Match mode 1 is for an exact match or the next value that is bigger than the search_key.
search_mode: 1
It means to search the lookup_range from the first entry to the last one.
The Logic Behind Filling Blank Cells with the Text from Cells Below
We have reached the pivot part of the formula that fills blank cells with text from the cells below.
You may please take a look at this screenshot.
The XLOOKUP searches row numbers (C1:C) in D1:D. Wherever matches, it returns the name from column A.
Several search keys in C1:C do not have matches in D1:D. For example, cell C4 contains # 4. It’s not available in D1:D.
The next value bigger than # 4 is 20 in D20. So the formula returns the name “Lisa” from the result_range (A1:A).
What is the result of this?
The formula happens to fill blank cells with text from the cells below.
