LAST ARRAY VALUE
Posted by PHIL on December 18, 2001 2:25 AM
HOW DO I FIND THE LAST ARRAY VALUE IN A VLOOKUP
I.E.
=VLOOKUP(1,TEST2,2,LAST ARRAY VALUE)
THANXS
PHIL
Posted by Aladin Akyurek on December 18, 2001 2:44 AM
Phil --
What do you mean? Maybe you could give an example.
Aladin
Posted by phil on December 18, 2001 2:49 AM
Sorry,
i'm using the start time for making product X the finish time for product Y - so i'm using a vlookup to find product Y's finish time, but if there are more than product Y's i'd like the last finish time in the collumn
e.g.
prod time
a 12:00
b 13:00
c 14:00
a 15:00
a 16:00
i'd like to lookup prod a and get the last time value which would be 16:00...
hth
phil
Posted by Aladin Akyurek on December 18, 2001 3:29 AM
Phil --
Assuming your sample data and taking product "a" as target,
=INDEX(B1:B6,SUMPRODUCT(MAX((A1:A6="a")*(ROW(A1:A6)))))
will give you what you want.
Aladin
============ Sorry, i'm using the start time for making product X the finish time for product Y - so i'm using a vlookup to find product Y's finish time, but if there are more than product Y's i'd like the last finish time in the collumn e.g. prod time
: Phil -- : What do you mean? Maybe you could give an example. : Aladin :
Posted by phil on December 18, 2001 3:43 AM
Thanxs...
Assuming your sample data and taking product "a" as target, =INDEX(B1:B6,SUMPRODUCT(MAX((A1:A6="a")*(ROW(A1:A6))))) will give you what you want. ============ : Sorry, : i'm using the start time for making product X the finish time for product Y - so i'm using a vlookup to find product Y's finish time, but if there are more than product Y's i'd like the last finish time in the collumn : e.g. : prod time : a 12:00 : b 13:00 : c 14:00 : a 15:00 : a 16:00 : i'd like to lookup prod a and get the last time value which would be 16:00... : hth : phil :
Posted by Seal on December 18, 2001 10:49 PM
Re: Thanxs...
So what does the Max(a1:a6)="A" do?? When I was trying to figure this formula out it hada result of 0. Can you tell me Alan what that is doing to enlighten my wee little mind!
ken
Posted by Aladin Akyurek on December 19, 2001 11:41 AM
Re: Thanxs...
ken
Ken --
It's not
Max(a1:a6)="A"
but rather
MAX((A1:A6="a")*(ROW(A1:A6)))
that is, the MAX of the numeric array that results from multiplying
(A1:A6="a") [ which is an array of logical values ]
with
(ROW(A1:A6)) [ which is an array of row numbers related to A1:A6].
See
for an explanation how SUMPRODUCT (and so-called array formulas) work.
Aladin
Posted by Seal on January 06, 2002 10:42 PM
Re: Thanxs...
Thanks Alan for a very direct and accurate answer! I have learned so much on this message board and appreciate you sharing your knowlegde!
Ken
;)
