In practice, it often becomes necessary to quickly find the value of the last (most) non-empty cell in a row or column of a table. Suppose, for example, that we have the following table with sales data for several branches:
Task: find the value of sales in the last month for each branch, i.e. for Moscow it will be 78, for Peter – 41, etc.
If there were no empty cells in our table, then the path to the solution would be obvious – it would be possible to count the number of filled cells in each row and then take the cell with this number. But branches work unevenly: Moscow was idle in March and August, the branch in Tyumen opened only in April, etc., so this method will not work.
The universal solution would be to use the function VIEW (LOOKUP):
This function has tricky logic:
- It in turn (from left to right) iterates over non-empty cells in the range (B2:M2) and compares each of them with the desired value (9999999).
- If the value of the next checked cell coincides with the desired one, then the function stops viewing and displays the contents of the cell.
- If there is no exact match and the next value is less than the desired value, then the function goes to the next cell in the line.
It is easy to figure out that if you set a sufficiently large number as the desired value, then the function will go through the entire line and, as a result, will give the contents of the last checked cell. For compactness, you can specify the desired number in exponential format, for example 1E+11 (1*1011 or one hundred billion).
If the table does not contain numbers, but text, then the idea remains the same, but “very large number” should be replaced with “very large text”:
In relation to the text, the concept of “large” means the character code. In any font, the characters go in the following order of increasing codes:
- Latin uppercase (AZ)
- Latin lowercase (az)
- Cyrillic uppercase (A-Z)
- Cyrillic lowercase (a-z)
Therefore, the lowercase “I” turns out to be the letter with the largest code, and the word from several consecutive “yayayay” will, conditionally, be “a very big word” – obviously “larger” than any text value from our table.
Like this. Not quite obvious, but a beautiful and compact solution. To search for the last non-empty cell in columns, it also works with a bang.
- Search and substitution by several conditions (VLOOKUP by 2 or more criteria)
- Search for closest similar text (max character matches)
- 2D table lookup (VLOOKUP XNUMXD)