Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
VLOOKUP FOR MULTIPLE VALUES | Setting up and Configuration of Excel | |||
Multiple X-Axis Values | Charts and Charting in Excel | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |