Find last "non-empty" text match in column using built in functions in excel

Question

The following question discusses to find last match in column

find last match in column using built in functions in excel

I need a way to find the last non empty value on an entry based on month columns.

https://i.sstatic.net/Gpnvg.jpg

So as per above Image,

I had used =LOOKUP(2,1/($A:$A=$D$3),$B:$B) this formula to get value for month but for Jan it showing correct but for Month Feb it showing Value 0 instead of ABS-143-002 because last value for Month Feb empty cell.

I tried too much but not getting perfect answer for this

Please, help me with this I need non empty last value from column B for selected month without using VBA.

Answer 1

Try this one:

Column C (Cell C2): =IF(B2<>"",IF(COUNTIF(C3:$C$15,A2),"",A2),"")

Later drag for the column (If the last one is not C15, just custom)

On Column E (E3): =VLOOKUP($D3,CHOOSE({2\1},$B$2:$B$15,$C$2:$C$15),2,0)

So you can look up backwards. Just drag it down.

Hope it helps

Answer 2

You also need to check that B:B is not empty.

=LOOKUP(2,1/(($A:$A=D3)*(LEN($B:$B)>0)),$B:$B)