Vlookup - last result across columns

[MOB]

Row 1 has alternate headings - Planned Undertaken Planned Undertaken............repeating

Column A has persons' names

I need a vlookup (?) that looks in column A for that persons name, and returns the last non-blank entry on that row, which is also under a heading "Undertaken"

Any ideas?!

TIA

 

[Aladin Akyurek]

=LOOKUP(REPT("z",255),INDEX($B$2:$Z$2,MATCH("name",$A$2:$A$100,0),0))

assuming that we are looking for a text value. Otherwise, replace REPT("z",255) with 9.99999999999999E+307.

 

[MOB]

Thanks - very nearly works, the only issue that it is returning the very last entry on that row.............I need it to return the very last entry on that row, so long as the text in row 1 is "undertaken"

Is this possible?

Thanks

 

[RoryA]

Perhaps this array formula:

=LOOKUP(REPT("Z",255),INDEX(IF($B$1:$Z$1="Undertaken",$B$2:$Z$100,NA()),MATCH("name1",$A$2:$A$100,0)))

or non-array entered:

=LOOKUP(2,1/($B$1:$Z$1="Undertaken")/(INDEX($B$2:$Z$5,MATCH("name1",$A$2:$A$5,0),0)<>""),INDEX($B$2:$Z$5,MATCH("name1",$A$2:$A$5,0),0))

 

[MOB]

Hi

First formula - appears to be going across to the final instance of row A containing "Undertaken", even if it is blank - I need the last non-blank

Second formula...............seems to work perfectly!!

Many thanks

 

[RoryA]

Are the 'blank' cells actually empty, or do they have formulas in?

 

[MOB]

They are empty

 

[RoryA]

I can't see offhand why it wouldn't have worked. You did array-enter it?

 

[MOB]

That would be it................oops.

Thanks for your help with this, saved a colleague considerable manual input each month.