INDEX & MATCH - Help please

L

litos_aldovea

I have problem with INDEX and MATCH

I have 3 columns of data, NAME, DATE,TEXT

NAME Date Text
Carlos 1/1/4 Hello
Carlos 2/1/4 Good
Carlos 5/6/4 Mountain
Carlos 6/6/4 CORRECT1
Peter 5/5/4 Friday
Peter 20/6/4 CORRECT2

date is in format dd/mm/yy

The formula that I need to use is

Look for CARLOS in the database, and retrieve TEXT when NAME is CARLO
and DATE is the LATEST (for Carlos of course).

In this example if I look for Carlos the data I want is CORRECT1
If I look for Peter the data I want should be CORRECT2

The formula I tried was

=index(Names,MATCH(1,(Names=Carlos)*(Dates=Max(Dates)),0)

I retrieve 0 all the time.

I think it is because it doesn't match the latest date for Carlos.
can't make it work.

Any ideas will be very much appreciated.

Thanks and regards,
Carlos
 
A

Andy B

Hi

Try this:
=INDEX(B1:C6,MATCH(MAX(IF(A1:A6="M",B1:B6)),B1:B6),2)
This needs to be array-entered (use Ctrl Shift and Enter instead of just
Enter)
 
D

Don Guillett

Another way to do this IF looking for a number instead of text, without
ARRAY entering
=SUMPRODUCT((R1:R5="c")*(S1:S5=MAX(S1:S5))*T1:T5)

--
Don Guillett
SalesAid Software
[email protected]
Andy B said:
Hi

Try this:
=INDEX(B1:C6,MATCH(MAX(IF(A1:A6="M",B1:B6)),B1:B6),2)
This needs to be array-entered (use Ctrl Shift and Enter instead of just
Enter)
Click to expand...
 
P

Peo Sjoblom

Try this array entered (ctrl + shift & enter)

=INDEX(Text,MATCH(1,(Names="Carlos")*(Dates=MAX(Dates)),0))

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
D

Domenic

Hi,

Try,

=INDEX(C2:C7,MATCH(MAX(IF(A2:A7="Carlos",B2:B7)),B2:B7,0))

entered using CTRL+SHIFT+ENTER.

Peo, I'm not sure if I'm doing something wrong but I wasn't able to get
your formula to work. I get a #N/A. And yes, I'm entering it as an
array formula. :)

Andy, it probably was an oversight on your part, but the only way I was
able to make your formula work is by changing the match type to 0. Then
it works perfectly. :)
 
Top