CODEWITHSUNDEEP
excelmatch
n8-da-gr8
n8-da-gr8

Reputation: 551

INDEX MATCH based on two criteria

I have a list of IDs and corresponding Plans. 

ID              Plan
123456          Ca1
456789          Ca2
456789          Ca3
34567           Ca1
67899           Ca1
67899           Ca5

I need to write a lookup which checks if the ID exists, and if it does, check to see if the Plan is equal to the column header, which is the plan (Ca1, Ca2, etc). If so, lookup returns 1, otherwise 0.

So, my final result will look like...

ID          Ca1     Ca2   Ca3   Ca5
123456      1       0      0     0
456789      0       1      1     0
34567       1       0      0     0
67899       1       0      0     1

My current lookup is below, where $B$1 is the column name. However, it only works for the last ID in the list and returns 0 for the others with that specific plan.

=IF(INDEX([Combined.xlsx]IDs!$A:$C,MATCH($B2,[Combined.xlsx]IDs!$A:$A,0),MATCH("Plan",[Combined.xlsx]IDs!$A$1:$N$1,0))=$B$1,1,0)

How can I adjust my formula to return 1 for all IDs which have the specified plan? The suggested formula did not work for my desired solution.

Upvotes: 0

Views: 123

Answers (2)

Harun24hr
Harun24hr

Reputation: 37125

You can also use Countifs() formula.

=COUNTIFS($A$2:$A$7,$D2,$B$2:$B$7,E$1)

enter image description here

Upvotes: 0

Michal
Michal

Reputation: 6121

SUMPRODUCT is the simplest solution.

enter image description here

Or even better with list objects:

enter image description here

Upvotes: 4

Related Questions