0

I'm wondering if someone would be able to offer some advice on the best way to do this please:

Data in Worksheet # 1 

Col A  | Col B | Col C
Part-1 |     8 |     2
Part-2 |     7 |     7           
Part-7 |     9 |     4
Part-8 |     2 |     6

Data in Worksheet # 2

Col A  | Col B | Col C
Part-1 |     8 | *Return value* (If Part-1 is found and 8 matches, return 2)         
Part-2 |     7 | *Return value*  
Part-3 |     8 | *Return value*

In Worksheet#2 in Cell C2 - I would like to check if the Part-1 in A1 is found in Col A in Worksheet#1. If it is, then I would also like to make sure the Number is B2 in Worksheet#2 matches the Qty in Col B next to the same part#, if both the Part# and Qty match, then i would like to copy across the value from the corresponding cell in Col C in Worksheet#1, to Col C in Worksheet#2. If it does not match, I would like it to return a 0.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
user2241640
  • 1
  • 1
  • 2
  • Can you reformat the question tables in your question? The spacing doesn't match up. Use two spaces at the end of the line to make a one line break and check out the preview window before you submit it. – wilsjd Apr 03 '13 at 18:20
  • If I have understood your question, then you can use a formula to compare two cells using `IF()` and then return the value of Col C if the data matches. – Siddharth Rout Apr 03 '13 at 18:26
  • I have tried to reformat, would you please le me know how this looks please. – user2241640 Apr 03 '13 at 19:23
  • Also im wondering if you would be able to give some more info in the IF () statement i could use please? – user2241640 Apr 03 '13 at 19:23
  • `IF`, `COUNTIF` and `AND` functions should be all you need here. –  Apr 03 '13 at 19:25
  • I have tried If, And, Exact etc and i haven't had any luck for some reason, i have tried reading many forums and help menus and can't seem to figure out what im not doing right. Thanks for much for your reply, however wondering if there would be any chance you could share some more info on this pleas? – user2241640 Apr 03 '13 at 19:29
  • I did some formatting as an example, please complete or remove any mistakes yourself. The help button explains you all about formatting. – Reinier Torenbeek Apr 03 '13 at 21:32

2 Answers2

1

Essentially, what you are doing is a lookup based on values in two columns. Looking at it from that angle, you can use a the SUMPRODUCT function, which is supported in any version of Excel. Enter the following in Sheet2, cell C2:

=SUMPRODUCT((Sheet1!$A:$A=$A2)*(Sheet1!$B:$B=$B2)*Sheet1!$C:$C)

Select and drag down the right-bottom corner of C2 to complete column C.

This only works by virtue of the fact that the values in Sheet1, column C, are numbers. It breaks if value pairs in column A and B of Sheet1 occur multiple times, but you did not address that situation in your question in the first place.

For versions 2007 and up, you can use the more convenient function SUMIFS with basically the same approach:

=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A1,Sheet1!$B:$B,$B1)

Alternatively, you can use a combination of IF and VLOOKUP functions. A formula that will work for Excel 2007 or newer is the following:

=IFERROR(IF(VLOOKUP($A1,Sheet1!$A:$C,2,FALSE)=$B1,VLOOKUP($A1,Sheet1!$A:$C,3,FALSE),0),0)

Older versions of Excel do not support IFERROR, but you can still use a similar approach as explained here.

I have uploaded an example workbook here, which includes an alternative method in column D of Sheet2 as well.

Reinier Torenbeek
  • 16,669
  • 7
  • 46
  • 69
  • Hi Reinier - Thank you so very much for your reply. This is my first time to a forum and am so gratful for the help from others you don't even know! Unfortunately i neglected to mention a very key point now im reading your msg, i have excel 2003 :( wondering therefore if there is any way to modify this to work in my version? – user2241640 Apr 03 '13 at 23:00
  • Welcome to StackOverflow. Please make sure to read the [FAQ](http://stackoverflow.com/faq), especially the section [How do I ask questions here?](http://stackoverflow.com/faq#howtoask) about voting for and accepting answers. I think the `SUMPRODUCT` approach should work with Excel 2003, did you try it? I uploaded a [version of the workbook save in old format](http://temp-share.com/show/Pf3Y1AbK20) but I have no way to verify it for 2003... – Reinier Torenbeek Apr 03 '13 at 23:22
1

Here is the a variation on Reinier's second approach in a form that will work in any version of Excel. You can use references to the specific data ranges, as I have done here, or to entire columns.

  =SUM((A2=Sheet1!$A$2:$A$5)*(Sheet2!B2=Sheet1!$B$2:$B$5)*Sheet1!$C$2:$C$5)

This is an array formula, so it needs to be entered with the Control-Shift-Enter combination. It performs the same operation as the SUMPRODUCT. (There are several other ways to do this, such as using MATCH with INDEX or OFFSET.)

chuff
  • 5,846
  • 1
  • 21
  • 26