Validate text from list using multiple sheets

0
votes

I am using google sheets to do the following.

Sheet 1 : 1 column for each person who needs access to the file. Each column's cell has a dropdown menu so people can select what items they have.

Sheet 2 : A list of every item in column A, columns B through G are the names of the people.

What I am trying to do is to have on sheet 2, the words "YES" or "NO" appear under each person's name if they have selected the item whatever the order.

So if Person 1 picks in the dropdown of sheet 1 that they have Item 1, Item 3, Item 2 in this order, I want sheet 2 to show the "YES" or "NO" mention. I don't want the order of the items in the list to be an issue.

So far, I have tried these 2 methods : 

=IF('Sheet1'!A2:A25=A2;"YES";"NO")

=IF(RegExMatch('Sheet1'!A2:A25;A2);"YES";"NO")

These do not work as the items must be selected in the same order as they appear in the second sheet. Is there another function that can validate a list in any order and apply the appropriate value?

Thanks ahead!

Jason

Edit : https://docs.google.com/spreadsheets/d/1cNn7G9x9o56d_9qM18s3AULkhpfOV5Y-b55vycCUyLY/edit?usp=sharing

1
google-sheets
Could you share your sheet/screenshot? - TheMaster
Link provided as edit in original post. - Jason_BD
Yes. The people will be listed in the exact same order as it appears on sheet 1 - Jason_BD

1 Answers

0
votes

Sheet2!B2: 

=ARRAYFORMULA(IF(ISERROR(MATCH($A$2:$A$100;Sheet1!A2:A25;0));false;true))
  • MATCH Sheet1A column against Sheet2A column
  • IF MATCH returns error, FALSE, else TRUE.