×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

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

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

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

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

(OP)
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.
 



 

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

(OP)
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

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

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

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

(OP)
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

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

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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources