Find last non blank cell & return column header

[Jamidd1]

Hi Excel Forum,

I am using the following formula to look for the last non blank cell in a range and return the text in the column header, it works well but i was wondering if it was possible to adapt it so that it searches for the last P value in range and then return value in the column header. Or even adapt it so it searches for the last value out of the following "P" "M" "D" and then returns the column header.

Please Login or Register  to view this content.

Thanks

Jamidd

[kvsrinivasamurthy]

Try this

=(LOOKUP(2,1/(ISNUMBER(FIND("P",UPPER(B4:N4)))+ISNUMBER(FIND("D",UPPER(B4:N4)))+ISNUMBER(FIND("M",UPPER(B4:N4)))),B1:N1))

[Jamidd1]

Thanks this works a treat!

Can i just add to it like so

(LOOKUP(2,1/(ISNUMBER(FIND("P",UPPER(B4:N4)))+ISNUMBER(FIND("D",UPPER(B4:N4)))+ISNUMBER(FIND("M",UPPER(B4:N4)))+ISNUMBER(FIND("1",UPPER(B4:N4)))+ISNUMBER(FIND("2",UPPER(B4:N4)))),B1:N1))

[kvsrinivasamurthy]

For "1" and "2" UPPER function not required. It can be used without UPPER function.

[Jamidd1]

I dont supposed anyone has a way to shorten down this formula a bit?
I have a few more values to add and it is getting a bit bulky to play with!

Thanks

Jamidd

[daddylonglegs]

Try like this:

=LOOKUP(1000,MATCH(B4:N4,{"P","D","M",1,2},0),B1:N1)

[Jamidd1]

This do not seem to be right, it is not returning a value in B1:N1, but i cant work out why!

[JohnTopley]

Works for me.


Post a file with incorrect result.

[Jamidd1]

Very strange! please see attached workbook.

It works on the example book that kvsrinivasamurthy but when i try this on the workbook ive attached i dont seem to get the same result!

Thanks

jamidd

[JohnTopley]

This

=LOOKUP(1000,MATCH(B2:N2,{"P","D","M",1,2},0),$B$1:$N$1)

B1:N1 must be ABSOLUTE addresses


To remove #N/A errors ..

=IFERROR(LOOKUP(1000,MATCH(B2:N2,{"P","D","M",1,2},0),$B$1:$N$1),"")

[Phuocam]

Try:

=IFERROR(LOOKUP(1000,MATCH(B2:N2,{"P","D","M",1,2},0),$B$1:$N$1),"")

[Jamidd1]

O OF COURSE!! Sorry this was me being silly, i did not notice i had not made them absolute references

Thanks John! much appreciated.

[JohnTopley]

You're welcome.

And thanks for the rep.