Trying to create a table where the columns are IDs, and the rows contain yes/no based on whether or not specific text is linked to the ID

Question

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!

Answer 1

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")