MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

8471.html

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
;)