Find last entry in row based on a matching value

[newbie]

PHYZX 5.26 5.29 5.31 5.33 5.33 5.33 5.33
NAMAX 11.27 11.34 11.41 11.51 11.57 11.54 11.40
JSVAX 13.45 13.59 13.58 13.68 13.66 13.66 13.42

Assuming PHYZX is at Sheet1!C2, I want to find the last entry for PHYZX from
within Sheet2 for use in a formula specific to PHYZX. Value PHYZX can be
sorted to any row depending on other criteria, so the formula must always
find the last value for PHYZX. I would like to do this for each row.

i would prefer not to use VB code.

 

[T. Valko]

Try this...

Data on Sheet2 in the range A1:H3.

=LOOKUP(1E100,INDEX(Sheet2!B1:H3,MATCH(C2,Sheet2!A1:A3,0),0))

 

[Max]

Your source data is in Sheet1, with C2 down containing key names eg: PHYZX,
and where corresponding values for each name are assumed filled from left to
right in col D across w/o intervening blanks

In another sheet,
Assume in A2 down are the name inputs eg: PHYZX
Put in B2, array-enter ie confirm the formula by pressing CTRL+SHIFT+ENTER
=INDEX(OFFSET(Sheet1!$D1:$IV1,MATCH(A2,Sheet1!$C:$C,0)-1,),MATCH(MAX(IF(OFFSET(Sheet1!$D1:$IV1,MATCH(A2,Sheet1!$C:$C,0)-1,)<>"",COLUMN(D$1:IV$1))),IF(OFFSET(Sheet1!$D1:$IV1,MATCH(A2,Sheet1!$C:$C,0)-1,)<>"",COLUMN(D$1:IV$1)),0))
Copy B2 down to return the rightmost value for the names in A2 down.
Success? Celebrate it, hit YES below