Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
(OP)
Problem; When values in column#1 & #2 of ws#1 match with values in column#1 & #2 of ws#2 the funtion returns the value in column#3 of ws#2 to column#5 of ws#1.
Ex; If A2=10 & B2="L" in ws#1 then the funtion returns 700 to cell# E2.
Wsheet#1;
A B C D E
1 0 J 20 26
2 10 L 60 75 700
3 10 J 75 85
4 12 J 70 85
5 12 L 80 100
Wsheet#2;
A B C
1 10 J 500
2 12 L 600
3 10 L 700
4 12 3.50 800
5 14 4.00 900
In advanced appreciate your help.
Thks
JPS
Ex; If A2=10 & B2="L" in ws#1 then the funtion returns 700 to cell# E2.
Wsheet#1;
A B C D E
1 0 J 20 26
2 10 L 60 75 700
3 10 J 75 85
4 12 J 70 85
5 12 L 80 100
Wsheet#2;
A B C
1 10 J 500
2 12 L 600
3 10 L 700
4 12 3.50 800
5 14 4.00 900
In advanced appreciate your help.
Thks
JPS





RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
one way: use the following array formula in cell e2 of ws#1
=MAX(IF(Sheet2!A1:A5=A2,IF(Sheet2!B1:B5=B2,Sheet2!C1:C5)))
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
or the following formula that can be normally entered ...
=MAX(INDEX((Sheet2!A1:A5=A2)*(Sheet2!B1:B5=B2)*(Sheet2!C1:C5),0))
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
Below is the exact example of what I am trying to do;
ws#1;
Grade Awidth Thick(mm) Length Price Tot_Cost
HD_PREM 4 5.50 96.00 $160.00
HD_UTIL 4 5.50 145.00 $94.00
KRONO_FLO 4.8 6.30 103.15 $193.00
MOULD_PREM 8 17.00 97.01 $396.00
MOULD_PREM 8 14.60 80.12 $449.00
ws#2
Grade AWidth Thick/mm Tot_Cost
HD_PREM 4.0 4.8 $132
HD_PREM 4.0 5.5 $152
HD_PREM 4.0 6.4 $173
HD_UTIL 4.0 4.8 $132
HD_UTIL 4.0 5.5 $152
KRONO_FLO 4.8 6.3 $174
KRONO_FLO 4.8 7.3 $197
MOULD_PREM 8.0 12.0 $285
MOULD_PREM 8.0 12.8 $302
MOULD_PREM 8.0 13.2 $311
MOULD_PREM 8.0 15.0 $345
MOULD_PREM 8.0 14.7 $372
MOULD_PREM 8.0 17.0 $391
The idea is to use ws#2 to find in ws#1 the Total_cost corrsponding to each Grade, Awidth and Thickness.
Cosidering the above Ex., the formula should extract from ws#2 the following Total_Cost values in ws#1;
Grade Awidth Thick(mm) Length Price Tot_Cost
HD_PREM 4 5.50 96.00 $160.00 $152
HD_UTIL 4 4.80 145.00 $94.00 $152
KRONO_FLO 4.8 6.30 103.15 $193.00 $174
MOULD_PREM 8 17.00 97.01 $396.00 $391
MOULD_PREM 8 14.60 80.12 $449.00 $372
Pl. note that in the case of the last record, the value of Thick(mm) in ws#1 does not exactly match Thick(mm) in ws#2... It would be great is the formula used can bring the closest higher value that finds on ws#2.
Thanks for your valuable help.
JPS
PS: I just saw that you sent me another option...I will try it an let you know if it works.
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
Thanks for your advise.
JPS
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
Both the formulas I posted work for the scenario in your original post.
I am sorry neither of the two formulas I posted worked for what you really want to do. However, let me ask you how does the data you posted originally match with what you really want to do per your subsequent posts.
Any way if you apply the principle in my solutions that I posted, you can develop a solution for your real problem.
Following is a table of cells F1:F6 from the results in WS#1 I received by developing a solution based on the principles of my first solution ( and I also took care of the exception to suit your last result by assuming an allowable variation of 2mm in Thickness ... mind you it is for you to set the specification, truly speaking I should not be taking the liberty of setting the specification for you).
Tot_Cost
152
152
174
391
372
array formula in cell F2 of WS#1 is ...
=MAX(IF(Sheet2!$A$1:$A$14=A2,IF(Sheet2!$B$1:$B$14=B2,IF(ABS(Sheet2!$C$1:$C$14-C2)<0.2,Sheet2!$D$1:$D$14))))
Before you change any thing else, let us first make sure that you can reproduce the resulting values in cells F2:F6 of WS#1 that I hve posted above ... and then let us take it from there.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
Regarding to your comments I have copied the formula you suggested from F2 to F6 in ws#1 ... and I'm still not sure what I am doing wrong but I keep getting "0's" as a result on every one of these cells.
Any possiblity of sending you the sample spread sheet I am working on (same one copied above) so you can perhaps find what is going on?
I very much appreciate all your help on this matter ... something I've been trying to solve for quite a time.
Thanks for your help
JPS
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
I am sorry you are still having difficulty using the formula I posted. That is an array formula which is entered with CTRL+SHIFT+ENTER rather than just with ENTER.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: Excel matches 2 values of ws#1 w/2 values of ss#2 to return one
Regarding to your suggestion, I must tell you that you are a genious !! and I am still amazed that IT WORKED GREAT !!! The only problem that still persists is that I am still getting a "0" value on cell F3 ?? ... and all the rest of the values are ok.
Do you know why this can happen?
Again thanks for your valuable help.
JP
PS: I still have not tried the formula with the real "spread sheets" but assume that it will work ... I will try to dedicate some time tomorrow and let you know.
Thanks.