IF function comparison

[dango000]

Hi,

In the file attached, there is the Group column (B) and Name column (C) both of which are related to each other. I need a formula that would return a value under certain conditions of what is inside columns B and C.

If e.g. the column B value is USA, but in column C we have "wood and metal" for group USA, then I need column D (Result) return "USA", same rule would apply for China. If I am trying to to get a result from groups - France, Greece, and Italy; then I want to return what is in Column C (oil,metal, TV) into column D

 

[cortexnotion]

Hey, to check I understand correctly:

- If ColB = USA, and ColC = wood or metal, ColD = USA
- If ColB = China, and ColC = wood or metal, ColD = USA
- If ColB = France, Germany Or Italy, ColD = ColC value

What should happen if the conditions aren’t met?

 

[dango000]

Hi Cortex, corrections are in bold

- If ColB = USA, and ColC = wood or metal, ColD = USA (correct)
- If ColB = China, and ColC = wood or metal, ColD = USA, --- colD=China, as the China group has different values in each columnC field (like for the USA group
- If ColB = France, Germany Or Italy, ColD = ColC value -- correct (oil for france, metal for greece, tv for italy)

Thus if the same Group value has different Name value in each row like US or China, colD returns whats in colB. But if the name value doesn't change in the cases of Italy, Germany, and France colD would return what is in colC

Thanks

 

[Fluff]

How about
=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,A2,B2)

 

[dango000]

Hi Fluff,

Thank you very much, it works!

But I don't quite understand how the countif part of the function works (COUNTIFS(A:A,A2,B:B,"<>"&B2)>0). Would greatly appreciate if you could explain the reasoning.

Thanks again!

 

[Fluff]

Cross posted If function comparison

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[dango000]

Sorry about that, would still appreciate if you could explain the reasoning of the formula.

Thanks,

 

[Fluff]

It's counting how many rows have A2 but not B2, so for row 2 it's counting how many rows have USA in col A, but not wood in col B (in this case 1).
If USA only existed once, or all instances of USA also had wood in col B the answer would be 0
HTH

 

[dango000]

Thank you, in this part of the function B:B,"<>"&B2)>0 how would it understand to return whats in column B?

 

[Fluff]

The B:B is looking at column B & the "<>"&B2 says not equal to B2