CODEWITHSUNDEEP
excelexcel-formula
hayder majid
hayder majid

Reputation: 3

Getting the last non-empty cell title in row - Excel 2019

I have this formula in excel 2019 to check the last non-empty title in row :

=IFNA(INDEX($BZ$1:$CN$1,MATCH(TRUE,BZ4:CN4<>"",0)),"No Data")

but it's return only the first non-empty cell from right(my table is from right to left) not the last non-empty cell to the left, for example : the title row is starting from BZ1 to CO1 and data row starting from BZ3 to CO3, and the cells CE3, CC3 and CA3 has values, the formula will give me only the CA3 title, not the CE3, so, how can i modify the formula to give me last title for non-empty cell (if i put value in CN3 it will give me the title of CN3 cell not the CA3)

I tried the formula and it's give me the first cell title from the right only, not the last one

Upvotes: 0

Views: 60

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

You could try one of the solutions as per your preference:

• Using LOOKUP() function:

 =IFERROR(LOOKUP(2,1/(BZ3:CO3<>""),BZ$1:CO$1),"No Data")

• Or a quick fix to your existing formula:

=IFNA(INDEX(BZ$1:CO$1,MATCH(2,1/(BZ3:CO3<>""),1)),"No Data")

NOTE: The first method doesn't requires to hit CTRL+SHIFT+ENTER but the second method does needs to hit while exiting the edit mode, however it depends on ones excel version. One more thing as per your post the title row is starting from BZ1 to CO1 hence in the example formula shown with BZ1:C01 and the data from BZ3:C03 you may need to change as per your actuals/originals.

Upvotes: 1

Related Questions