Created
January 30, 2023 15:18
-
-
Save palashkulsh/2d4321bc1d82211b2c9e9e065d974c74 to your computer and use it in GitHub Desktop.
excel complex formula
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
If you want to find last value of the column | |
K1 is start of upper header, U6 is end of current row | |
we want to find header of last non empty column in a row (for K to U columns) | |
=ifna(index(filter($K$1:U6,not(ISBLANK(K6:U6))),1,counta(K6:U6)),"TBD") | |
This selects subset of non empty cells from K1 to U6 (from first row to current row) | |
FILTER AREA | |
=filter($K$1:U6,not(ISBLANK(K6:U6))) | |
from these non empty cells | |
index(reference area,row, column) | |
from filter area we select first row, and last non empty element (counta(K6:U6) gives length or count of non blank elements) | |
=index(FILTER AREA, 1, counta(K6:U6)) | |
then remove #NA with TBD | |
=ifna(value, "TBD") | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment