Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
maacmaac
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
pinmaster
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup for data contained in a cell Garbunkel Excel Worksheet Functions 5 September 14th 05 06:47 PM
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM
Table Array in VLOOKUP Relies on Data Validation willydlish Excel Worksheet Functions 2 February 16th 05 04:20 AM
How to use a cell value as Table Array in VLOOKUP worksheet function willydlish Excel Discussion (Misc queries) 2 February 16th 05 03:47 AM
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question Barb Reinhardt Charts and Charting in Excel 3 December 8th 04 02:48 AM


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"