Lookup matching 3 values to 3 others and returning a value

[trebor200]

Hi

I am new to the forum and would apreciate some help / guidence i
possible

I have a scnario where i have 2 sheets, and in the first sheet (Data)
want to return a value to cell D3 from an orderbook in orderbook tab

Data Tab: I need to match a part number in B3 and year in D1 and Mont
in D2 then match these to values in orderbook tab and return the value

I have attached an example for reference, hopefully someone can mak
sense of this, much appreciated for any help

Excel 200

+-------------------------------------------------------------------
|Filename: Trial.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=310
+-------------------------------------------------------------------

 

[Spencer101]

Hi,

I am new to the forum and would apreciate some help / guidence i
possible.

I have a scnario where i have 2 sheets, and in the first sheet (Data)
want to return a value to cell D3 from an orderbook in orderbook tab.

Data Tab: I need to match a part number in B3 and year in D1 and Mont
in D2 then match these to values in orderbook tab and return the value.

I have attached an example for reference, hopefully someone can mak
sense of this, much appreciated for any help.

Excel 2007

Hi,
This is easily done using INDEX and MATCH but it would rely on a sligh
change to your spreadsheet.

Rather than having just a month in row 2 and a year in row 1, if yo
combined them so the date in B2 on the orderbook tab showed as Jan-1
and all date cells followed that format, including the date cells on th
data tab. Would that be suitable for you?

If so I will show the formula you need. If not I will have anothe
think. :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[trebor200]

Hi,
This is easily done using INDEX and MATCH but it would rely on a sligh
change to your spreadsheet.

Rather than having just a month in row 2 and a year in row 1, if yo
combined them so the date in B2 on the orderbook tab showed as Jan-1
and all date cells followed that format, including the date cells on th
data tab. Would that be suitable for you?

If so I will show the formula you need. If not I will have anothe
think.

Hi Thanks for your reply, that sound like a good idea, i am not sure ho
to use index and match as i usually just use the Vlookup and Hlookup.

I would be bring the date in via SQL into a Excel template where a pivo
table would be updated using refresh or a macro, the reason for th
layout of the spreadsheet that it would be a pivot table of th
order-book.

If we could not format in pivot table i could lok at bring the data int
the spreadsheet via query and change the date there to Jan-12.

Ill have a look at this also

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Vacuum Sealed]

Hi,

I am new to the forum and would apreciate some help / guidence if
possible.

I have a scnario where i have 2 sheets, and in the first sheet (Data) i
want to return a value to cell D3 from an orderbook in orderbook tab.

Data Tab: I need to match a part number in B3 and year in D1 and Month
in D2 then match these to values in orderbook tab and return the value.

I have attached an example for reference, hopefully someone can make
sense of this, much appreciated for any help.

Excel 2007


+-------------------------------------------------------------------+
|Filename: Trial.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=310|
+-------------------------------------------------------------------+

Hi

Not sure I agree with your layout, but then I am not privy to what it is
your doing with the sheets. That Said! from what I can gather you only
need to do the following:

In Sheet("Data") - Cell("D3") formula is =Orderbook!B3
This gives a result: Jan = 7, which-in-turn gives your other formulas
the following: Fit = 18, Weld = 21

So, to expand this:

Jan-D3: =Orderbook!B3
Feb-G3: =Orderbook!C3
Mar-J3: =Orderbook!D3
Apr-M3: =Orderbook!E3
May-P3: =Orderbook!F3
Jun-S3: =Orderbook!G3
Jul-V3: =Orderbook!H3
Aug-Y3: =Orderbook!I3
Sep-AB3: =Orderbook!J3
Oct-AE3: =Orderbook!K3
Nov-AH3: =Orderbook!L3
Dec-AK3: =Orderbook!M3

Select D3:AM3 and Copy
Select D48 then Right-Click | PasteSpecial | Formula's

Then you can copy the entire range of D3:AM8 and select AN3 and and
repeat PasteSpecial Formula's to 2013. It should continue the Formula's
with no problems as they are not absolute.

HTH
Mick.

I am heading away for a few days so if this is not what you are looking
for be patient and one of the other contributors may help.

 

[Spencer101]

Hi Thanks for your reply, that sound like a good idea, i am not sure ho
to use index and match as i usually just use the Vlookup and Hlookup.

I would be bring the date in via SQL into a Excel template where a pivo
table would be updated using refresh or a macro, the reason for th
layout of the spreadsheet that it would be a pivot table of th
order-book.

If we could not format in pivot table i could lok at bring the data int
the spreadsheet via query and change the date there to Jan-12.

Ill have a look at this also,

Right, if you change the date formats so on the "Data" tab cell *D2
shows as *Jan-12*, *G2* is *Feb-12*, *J2* is *Mar-12* etc...

Then on the "Orderbook" tab, *B2* is *Jan-12*, *C2* is *Feb-12*, *D2* i
*Mar-12* etc...

The formula you'll need in cell D3 on the Data tab is :

-=INDEX(ORDERBOOK!$A$2:$Y$8,MATCH(DATA!$A3,ORDERBOOK!$A$2:$A$8,0),MATCH(DATA!D$2,ORDERBOOK!$A$2:$Y$2,0))

Make sure all the $'s are in the right place and you will be able t
copy this formula to all of the orange cells in rows 3-8 on the Data ta
and it's job done.

If you need any help with that the formula is actually doing just let m
know

Micks suggestion above would work too, and would probably be simpler
but if you ever have to change the dates, add 'parts' to the list or
number of other changes, then this approach will be more easily adapte
to fit that

+-------------------------------------------------------------------
+-------------------------------------------------------------------