IF function comparison

dango000

New Member
Joined
Apr 6, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

1586973771996.png
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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?
 
Upvote 0
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
 
Upvote 0
How about
=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,A2,B2)
 
Upvote 0
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!(y)
 
Upvote 0
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.
 
Upvote 0
Sorry about that, would still appreciate if you could explain the reasoning of the formula.

Thanks,
 
Upvote 0
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
 
Upvote 0
Thank you, in this part of the function B:B,"<>"&B2)>0 how would it understand to return whats in column B?
 
Upvote 0
The B:B is looking at column B & the "<>"&B2 says not equal to B2
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top