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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"maacmaac" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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))))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--