VLOOKUP to search multiple values?

  • Thread starter Thread starter Q[kjoe]
  • Start date Start date
Q

Q[kjoe]

I have the following problem:

I have a sheet containing the following info (resulting from an acces
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
way that I can copy it to all the other cells
 
Hi, Use this:

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

Where:
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
 
Back
Top