Excel matches 2 values of ws#1 w/2 values of ss#2 to return one

[jpspoerer]

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

 

[yogia]

Hi JPS:

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

 

[yogia]

Hi JPS:

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

 

[jpspoerer]

Yogi thanks for your advice. Unfortunately I did try what yo suggested but for some reason did not work.
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.

 

[jpspoerer]

I have tried both suggestions but none worked. It seems that the MAX array formula won't do it ??... Do you think that VLOOUP might do it ?
Thanks for your advise.
JPS

 

[yogia]

Hi JPS:

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

 

[jpspoerer]

Yogia thank you very much for your follow up and sorry for not being able to reply earlier.

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

 

[yogia]

Hi JPS:

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

 

[jpspoerer]

Yogia ... sorry again for my late reply but today I am travelling in Canada ...
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.