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.

--
newbie

see your other post

--
Biff
Microsoft Excel MVP


"newbie" wrote in message
...
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.

--
newbie

This might be adaptable to your needs. (I do not know where I came upon
this, so apologies to the author)

=INDEX(C1:C13,LARGE((B1:B13=A1)*ROW(A1:A13),COUNTI F(B1:B13,A1)+1-E2))

When you use this formula you must enter with Ctrl + Shift + Enter. Excel
will put curly brackets { } around the formula.

And a cell with this formula in it.

=COUNTIF(B1:B13,1)

So, to test fly this, do this:

A1 has the lookup value, I'm using 1 in my sheet.
B1:B13 holds various values to be looked up (PHYZX, etc)
C1:C13 holds the values to be returned
E1 has the COUNTIF formula
G1 holds the INDEX formula above (Ctrl + Shift + Enter)

Note that in the formula it refers to A1:A13 but on the sheet, only A1 will
have a value in it, the lookup value. I believe the reason for this is
because the ranges must be of equal size for the formula to work.

So put your PHYZX, NAMAX, JSVAX in B1 to B13
Put what you want to return for each of the B entries into C1:C13

E1 will count the number of times a lookup value occurs in column C and
return a value which I call an 'instance'.

So if I have four 1's in column B then the formula in E1 will return 4 and
G1 will return the value adjacent to the fourth instance of 1 in column B.

Post back if needed.

HTH
Regards,
Howard

"newbie" wrote in message
...
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.

--
newbie