0

I have a sheet named Test with columns

[Category],[SubCategory],[Name], [Is in share?]

that need to be compared(except for the column [Is in share?]) with the sheet named Share. Share also has the column [Category], [SubCategory], [Name]

If values in Test's columns [Category],[SubCategory],[Name] are matched with values in Share's columns [Category], [SubCategory], [Name] then return true. The value true should be in the column [Is in share?]

I tried to use vlookup but not getting it right.

Sheet Test
[Category]  [SubCategory]       [Name]        [Is in share?]
Food      |||  Hard      |||    FoodHard1 ||| False
Food      |||  Hard      |||    FoodHard2 ||| True
Food      |||  Soft      |||    FoodSoft1 ||| False
Table     |||  Wood      |||    TableWood1||| True

.

Sheet Share
[Category]   [SubCategory]      [Name]         [Date]         [Site]
Food      |||  Hard      |||    FoodHard23 ||| Jan-02-2003   ||| AB
Food      |||  Hard      |||    FoodHard2  ||| Jan-02-2003   ||| CA
Food      |||  Soft      |||    FoodSoft15 ||| Jan-05-2003   ||| KK
Table     |||  Wood      |||    TableWood1 ||| Jan-05-2003   ||| AB
3
  • What is your vlookup formula? What is the output vs the expected output? Sep 11, 2014 at 8:57
  • =IF(VLOOKUP(A2&B2&C2,Share!A:D,2,FALSE)=A2,TRUE,FALSE) This is the other way of doing things. I accepted @JNevill answer. I compared mine and his, both ways are correct. Sep 12, 2014 at 5:08
  • If you have another answer that works, go ahead and post it as an answer. You can leave the first answer as accepted, but comments are volatile and may disappear, so it's best to put it in an answer. Sep 12, 2014 at 11:29

1 Answer 1

0

Use =sumproduct() This will test multiple conditions of a range of rows. Each condition returns a 1 or 0 and they are then multiplied together to give a 1 (all conditions match) or a 0 (one or more conditions did not match).

=sumproduct((Test!$A1=Share!$A$1:$A$100)*(Test!$B1=Share!$B$1:$B$100)*(Test!$C1=Share!$C$1:$C$100)*1)

That formula assumes your data starts in A1 and has no head rows. It contains three conditions, each comparing Category, Subcategery, and Name in the two sheets, iteratively for each row 1 through 100 (change as needed). The final *1 just makes the formula return a 1 or 0. You could wrap that in an =if() statement to say if the result is 1 then return true else return false.

There are other ways to do this in newer versions of excel with =countifs() and =sumifs and in all versions of excel with an array formula.

3
  • =IF(VLOOKUP(A2&B2&C2,Share!A:D,2,FALSE)=A2,TRUE,FALSE) This is the other way of doing things. I accepted @JNevill answer. I compared mine and his, both ways are correct. Thanks JNevill Sep 12, 2014 at 5:09
  • but first it must have a helper column in sheet Share. =B2&C2&D2 Sep 12, 2014 at 5:15
  • If you are using a newer version of excel you might also want to look at =countifs(). It's must like sumproduct in that you can test multiple conditions. Furthermore, with the concatenated vlookup key example, you can eliminate the if statement and just use VLOOKUP(A2&B2&C2,Share!A:D,2,FALSE)=A2 being a boolean statement it will return a TRUE or FALSE on it's own.
    – JNevill
    Sep 12, 2014 at 14:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .