Vlookup based on 2 criteria

L

litos_aldovea

Hi all,

I have a table with the following structure

STORE NAME, DATE,CONTACT NAME
Mini,17/2/4,John
Mini,18/2/4,Lucas
Mini,23/5/4,Andrea
Mini,26/6/4,Joan
Mini,27/7/4,Carlos

Is it posibble to create a formula based on STORE NAME and "THE LATES
DATE" to retrieve the contact name?

Thanks in advance,
Carlos
 
R

Rutgers_Excels

Okay, here's the deal. I'm not as good of a user as 95% of the peopl
who post to this board. So the answer I am going to give you may no
be the best and it may be very round-a-bout. However, I think i
works.

Suppose you had this scenario....

Column A has Store Names
Column B has Dates
Column C has Contact Names
All of your information is listed from rows 1 through 9.

If you are looking for a formula that will allow you to find th
contact name based on the store name (Say, Mini) for the latest date
try this array function...

=INDIRECT("C"&MAX(IF(A1:A5="Mini",ROW(B1:B5))))

If you are unfamiliar with array functions, you need to press {control
shift, enter} when you enter the function into the cell.

Hope this helps
 
D

Domenic

Hi Carlos,

Try,

=INDEX(C2:C6,MATCH(1,(A2:A6="Mini")*(B2:B6=MIN(B2:B6)),0))

to be entered using CTRL+SHiFT+ENTER, and

adjust the ranges to suit your needs

Hope this helps!
 
D

Domenic

Sorry! Made a mistake! For the latest date, the formula should be:

=INDEX(C2:C6,MATCH(1,(A2:A6="Mini")*(B2:B6=MAX(B2:B6)),0)),
CTL+SHIFT+ENTER

Hope this helps!
 
L

litos_aldovea

Thanks a lot for your help,

... but I can't make it work. It is probably because I don't understun
the logic behind the formula you have provided (and EXCEL Help doesn'
help a lot to be honest).

Could you please provide more info about INDIRECT?

In the problem that I have explained before the data to extract fo
"MINI" is "Carlos".

I have been able to make it work with SUMPRODUCT to extract numbers
but when I use this solution to extract the CONTACT NAME doesn't wor
(Excel gives me a 0 value - totally understable as I guess SUMPRODUC
is only for values).

Thanks again,
Carlos
 
R

Rutgers_Excels

The indirect function will link you to a cell indirectly. Say, fo
instance, cell A1 contains the text "B1". In cell B1 you have th
number 100. If you type in =INDIRECT(A1) in cell C1, you will b
indirectly linked to cell B1 and your answer in C1 will be 100.

With that said, I was assuming that your data for Contact Names wer
located in column C. That is what the first part of that is...
INDIRECT("C"....

The rest of the formula is an array function. What it does is find
the row for the maximum date (latest date) where the store name i
"Mini".

I assumed that your data below starts in A1 and goes through C
(headings excluded). So the max date where the store name is mini i
located in the 5th row. Thus, the answer to the array function is 5.
Therefore, INDIRECT("C"&[array function]) is actually like sayin
INDIRECT("C"&5) or INDIRECT(C5). The name Carlos is located in C5 an
that is what your answer will be. If your data is in different row
and columns, you will need to adjust the formula. Also, be sure t
press{control,shift,enter} when you enter the data or it will no
work.

I hope this cleared it up for you
 
L

litos_aldovea

Done.....with indirect.

I have to say that both methods are perferctly working.

Thanks a lot for your time and help.

Carlos
 
L

litos_aldovea

Ok, that was easy for you guys but.....

I have now another problem totally related to this matter.

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 forula 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
 
Top