如何在列中查找多个值

I have problem with matching in excel file. Let me explain it on an example:

我在excel文件中匹配有问题。让我以一个例子来解释它:

COL1 COL2    COL3 COL4 COL5 
AA11 First   AA11 ???? ???
BB22 First   BB22 
AA11 Second
BB22 Third

1. COL1 - User's indexes

COL1 - 用户索引

2. Col2 - User's groupName

Col2 - 用户的groupName

3. Col3 DISTINCT user's indexes

Col3 DISTINCT用户的索引

4. Col4,5... Values that i'm looking for

Col4,5 ......我正在寻找的价值观

I'm looking for formulas for following cases:

我正在寻找以下案例的公式:

Column 4: IF user with index COL3 has groups(COL2) 'FIRST' AND 'SECOND' add value = 'LEVEL1'

第4列:具有索引COL3的IF用户具有组(COL2)'FIRST'和'SECOND'添加值='LEVEL1'

Column 5: IF user with index COL3 has groups(COL2) 'FIRST' AND 'THIRD add value = 'LEVEL2'

第5列:具有索引COL3的IF用户具有组(COL2)'FIRST'和'THIRD add value ='LEVEL2'

1 个解决方案

#1

I would use COUNTIFS wrapped in an IF:

我会使用包含在IF中的COUNTIFS:

Column 4:

=IF(SUM(COUNTIFS(A:A,C1,B:B,{"First","Second"}))>=2,"Level 1","")

Column 5:

=IF(SUM(COUNTIFS(A:A,C1,B:B,{"First","Third"}))>=2,"Level 2","")