Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Vlookup Table
I am working with a spreadsheet with only 3 columns. Column A = Product Number; Column B = Date; Column C = Price (the Price on the Date found in column B). The problem I am running into is that I am working with ~50 Product Numbers with 3 years of price data for each. The data is located in a separate spreadsheet. The data for each product is located in separate tabs. For example, the sheet may look like following: Product Number.......Date.................Price 1456......................01/12/02...........??? 1456......................02/06/05...........??? 83734....................11/22/03............??? 83734....................03/01/04............??? 12.........................05/30/02...........??? I want to use a vlookup to pull in the price for the Date in Column B, but I don't know who to make the formula point to the correct tab. Thanks for any help. -- maacmaac ------------------------------------------------------------------------ maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959 View this thread: http://www.excelforum.com/showthread...hreadid=499250 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Vlookup Table
Here's one approach to try ..
A sample construct is available at: http://www.savefile.com/files/5544829 Complex_Vlookup_Table_maacmaac_misc_v2.xls Assuming the source data is in sheets named with the respective product numbers, i.e. named as: 1456, 83734, 12, etc, all sheets with identical format: data in cols A to B from row2 down to say, row1000 [~ 3 years worth], and with Col A = Date, Col B = Price (I've assumed there's only 2 cols, since the product number would already be on the sheet tab) In a sheet: Query, you have in cols A to C Product Number.......Date.................Price 1456......................01/12/02...........??? 1456......................02/06/05...........??? 83734....................11/22/03............??? 83734....................03/01/04............??? 12.........................05/30/02...........?? Put in C2, then array-enter the formula, i.e. press CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(OR(A2="",B2=""),"",IF(ISERROR(MATCH(TRUE,(INDI RECT("'"&A2&"'!A2:A1000")= B2),0)),"No match found",INDEX(INDIRECT("'"&A2&"'!B2:B1000"),MATCH(T RUE,(INDIRECT("'"&A2&"'!A2 :A1000")=B2),0)))) Copy C2 down. Col C will return the required results. Adapt to suit. Use the smallest possible range which is large enough to cover the max extent of data (per product) in all 50 product sheets, viz.: minimize the ranges: "A2:A1000", "B2:B1000" which is used in the formula. The 1000 rows assumed may have been excessive. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "maacmaac" wrote in message ... I am working with a spreadsheet with only 3 columns. Column A = Product Number; Column B = Date; Column C = Price (the Price on the Date found in column B). The problem I am running into is that I am working with ~50 Product Numbers with 3 years of price data for each. The data is located in a separate spreadsheet. The data for each product is located in separate tabs. For example, the sheet may look like following: Product Number.......Date.................Price 1456......................01/12/02...........??? 1456......................02/06/05...........??? 83734....................11/22/03............??? 83734....................03/01/04............??? 12.........................05/30/02...........??? I want to use a vlookup to pull in the price for the Date in Column B, but I don't know who to make the formula point to the correct tab. Thanks for any help. -- maacmaac ------------------------------------------------------------------------ maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959 View this thread: http://www.excelforum.com/showthread...hreadid=499250 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Vlookup Table
A sumproduct would be more suited to your needs I think but without more details it's hard to tell. Give more details of your setup like spreadsheet names, ranges of your data, stuff like that and I'm sure you'll get an answer to your problem. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=499250 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Vlookup Table
Oops, just detected there was an unnecess. pair of parens in the formula
around the lookup_array in MATCH (albeit functionality is not impaired) .. Put instead in C2, then array-enter the formula: =IF(OR(A2="",B2=""),"",IF(ISERROR(MATCH(TRUE,INDIR ECT("'"&A2&"'!A2:A1000")=B 2,0)),"No match found",INDEX(INDIRECT("'"&A2&"'!B2:B1000"),MATCH(T RUE,INDIRECT("'"&A2&"'!A2: A1000")=B2,0)))) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup for data contained in a cell | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel | |||
Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel |