So, I had data like this, its pretty easy to do in python but not sure why its not working in excel. I used this formula. I want to classify lead locations into team column.
Input lead location and pre-defined apac, emea and america list.
expected output: Team column
Is there any way to do it?
False? i dont get it...
=IF(COUNTIF($D2,E:E)>0, "APAC", (IF(COUNTIF($D2,F:F)>0, "EMEA", IF(COUNTIF($D2,G:G)>0, "NONE" ))))
| Lead Location | APAC | EMEA | AMERICA | Team |
|---|---|---|---|---|
| Dubai | Dubai | Toronto | Warsaw | APAC |
| Bangalore | Bangalore | Brazil | Frankfurt | APAC |
| Mumbai | Mumbai | Peru | APAC | |
| Warsaw | AMERICA | |||
| Frankfurt | AMERICA | |||
| Bangalore | APAC | |||
| Toronto | EMEA | |||
| Brazil | EMEA | |||
| Peru | AMERICA | |||
| Bangalore | APAC |
CodePudding user response：
Try below formula-
=INDEX($B$1:$D$1,MATCH(1,MMULT(--($B$2:$D$11=A6),TRANSPOSE({1,1,1})),0))
