Complex Vlookup Table

[maacmaac]

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.

 

[Max]

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,(INDIRECT("'"&A2&"'!A2:A1000")=
B2),0)),"No match
found",INDEX(INDIRECT("'"&A2&"'!B2:B1000"),MATCH(TRUE,(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.

--

 

[pinmaster]

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

 

[Max]

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,INDIRECT("'"&A2&"'!A2:A1000")=B
2,0)),"No match
found",INDEX(INDIRECT("'"&A2&"'!B2:B1000"),MATCH(TRUE,INDIRECT("'"&A2&"'!A2:
A1000")=B2,0))))