I would like the result to look something like this.
| ID1 | ID2 | ID3 | |
|---|---|---|---|
| Text 1 | yes | yes | no |
| Text 2 | yes | no | no |
| Text 3 | no | yes | yes |
And I have a table that looks like this. Notice that the text/strings can exist under multiple different IDs. Is there a way to use index/match for this? I've tried to use index/match, but to be honest I don't understand it that well.
| ID1 | ID2 | ID3 |
|---|---|---|
| Text 1 | Text 1 | Text 3 |
| Text 2 | Text 3 | null |
| null | null | null |
See above. I tried to use index/match, and it's just not making sense to me.
Edit: updated the tables so that values match. Sorry!
CodePudding user response：
Use a nested INDEX(MATCH) to return the correct column to a MATCH. Then test if the return is numeric:
=IF(ISNUMBER(MATCH($E2,INDEX($A$2:$C$4,0,MATCH(F$1,$A$1:$C$1,0)),0)),"yes","no")
