CODEWITHSUNDEEP
excellistindexingexcel-formulaunique
Jan
Jan

Reputation: 47

How to extract unique values from one column based on criteria from two other columns, with or statement?

My data set looks something like this: 

ID  Name1   Name2
1   Jack    Tom
1   Tom     Tom
1   Lisa    Tom
2   Tom 
2   Tom 
3   Frank   Frank
3   John    Frank
3   Frank   Frank
3   John    Frank
4           Tom
4           Tom
5   Lisa    
5   Jack

and I want the following output: 

Result
1
2
4

Note: I want the unique IDs for Tom if "Tom" shows in one of the two name columns.

I tried to use the following formula: 

IFERROR(INDEX(INDIRECT($B$14); MATCH(0; IF($B$10=INDIRECT($B$16); IF($B$10=INDIRECT($B$15); COUNTIF($E$27:E27; INDIRECT($B$14)); "")); 0));"")

The problem is that this only gives me ID nr 1 as output since Tom shows up in both columns in this case. I think I need to implement an OR-statement to the formula.

Explanation of my formula:

Good answers will be rewarded:)

Upvotes: 1

Views: 245

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

I used your formula (without INDIRECT statements) and added ISNUMBER & FIND in order to find "Tom" in a combination of columns B and C:

This is an array formula (Ctrl+Shift+Enter):

=IFERROR(INDEX($A$1:$A$14,MATCH(0,COUNTIF($F$1:F1,IF(ISNUMBER(FIND("Tom",$B$1:$B$14&$C$1:$C$14)),$A$1:$A$14,"")),0)),"")

Result:

enter image description here

I couldn't use INDIRECT references as I'm not sure what exactly they point to (i.e. what are the ranges & column names). I hope it won't be too difficult for you to modify my formula in order to match your references.

Hope it helps! Cheers.

Upvotes: 1

Related Questions