VLOOKUP to search multiple values?
 

I have the following problem:

I have a sheet containing the following info (resulting from an access
query):


Code:
--------------------

Project date Amount
A 31/03/2001 120
A 30/06/2001 100
A 30/09/2001 70
A 31/12/2001 50
A 31/03/2002 30
A 30/06/2002 0
B 30/06/2001 100
B 30/09/2001 75
B 31/12/2001 65
B 31/03/2002 55
B 30/06/2002 40
.. ....

--------------------

And another sheet containing:

Code:
--------------------

Project 31/03/2001 30/06/2001 30/09/2001 31/12/2001 31/03/2002 30/06/2002
A [X] [Y]
B [Z]
C

--------------------


On the spot of the [x], I want the value of "120", on [Y] I want "100",
on [Z] the value of "100", etc.. Which formula can I use for that, in a
way that I can copy it to all the other cells?


--
Q[kjoe]
------------------------------------------------------------------------
Q[kjoe]'s Profile: http://www.excelforum.com/member.php...o&userid=23763
View this thread: http://www.excelforum.com/showthread...hreadid=374140

Hi, Use this:

=INDEX(IF($E2=$A$15:$A$25,$C$15:$C$25),MATCH(F$1,I F($E2=$A$15:$A$25,$B$15:$B$25,0)))

Whe
E2 is the Project in the table
A15:A25 is the list of projects in your data.
C15:C25 is the list of 'Amounts'
F1 is the dates in the table
and B15:B25 is the list of dates

Enter this with ctrl+shift+enter (instead of just pressing enter), and
then drag the cell across the table.

If a date is not available for the project it returns FALSE

Hope this helps!

Alastair


--
spog00
------------------------------------------------------------------------
spog00's Profile: http://www.excelforum.com/member.php...o&userid=20197
View this thread: http://www.excelforum.com/showthread...hreadid=374140