Excel formula for unique list that also checks / matches one of two possible values in separate column

0
votes

I am struggling to extend the basic index match formula =index(A:A,Match(0,$X$1:X1,0)) to also consider if a value in column B is either "No" or empty ("").

For example, in the below table, in the c column, I can get as far as uniquely listing IDs with a "No" value via the following formula: =Index(A:A,match(0,countif($c$1:c1,A:A)+("No"<>B:B),0))

enter image description here

But what I can't seem to get to work is something that tries to consider if the value in B is either "No" or "". =Index(A:A,match(0,countif($c$1:c1,A:A)+OR(""<>B:B;"No"<>B:B),0))

I think the logic is ok here, but excel does not treat the OR part of the formula like an array.

Is there a way in excel's index match unique list method to also consider if a cell in the same row contains two potential values?

1
excellistmatchunique

1 Answers

1
votes

I think there may be a more compact version but this provides the correct results.

=IFERROR(INDEX(A$2:A$9, MATCH(0, IF(B$2:B$9="No", COUNTIF(D$1:D1, A$2:A$9), IF(B$2:B$9="", COUNTIF(D$1:D1, A$2:A$9))), 0)), "")

Once entered correctly¹, fill down as necessary.

        unique_list_two_conditions

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.