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

0
votes

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://imgur.com/a/Sf5YvUI

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.

2
excelexcel-formula
Can you show up your formula? This LOOKUP is ok with numbers, but as far as I know will not work with strings as there is no "bigger" value. – David García Bodego
@DavidGarcíaBodego Not so. The values in lookup_vector can be text, numbers, or logical values. or errors, for that matter. – Ron Rosenfeld

2 Answers

3
votes

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

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

enter image description here

1
votes

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