Hi InnerCircle members,

I have a table(i) like this:

I want to create a new table, which shows what kind of cert a person passed:

I tried to use Excel functions:

VLOOKUP returns the 1st result it searches. I got the wrong results. For instance, it returns No in AWS for [email protected], because CompTIA is the 1st value.

Does anyone know how to fix this? Thanks for your time and your help.

CodePudding user response：

You may try this way, using FILTER() Function,

• Formula used in cell D1

=UNIQUE(A2:A5)

• Formula used in cell E1

=IF(FILTER($B$2:$B$5,($D2=$A$2:$A$5)*(E$1=$B$2:$B$5),"")=E$1,"Yes","No")

And Fill Down & Drag Right!

You can also use the COUNTIFS() Function as well for Yes & No,

=IF(COUNTIFS($A$2:$A$5,$D2,$B$2:$B$5,E$1)>0,"Yes","No")