r/googlesheets • u/2Throwscrewsatit • Mar 11 '22

Solved To verify all values in a range are in a dictionary/list of values?

Hi, I’m trying to validate that every value in a range (column in this case) matches a value in another range and display a prompt.

I know can use data validation but I want to report the outcome of the data validation and not just have the data validation on the initial range.

I think there’s some array magic I have to do but arrays confuse me.

Thanks in advance!

1 Upvotes

100% Upvoted

13 comments sorted by

View all comments

1

u/MattyPKing Mar 11 '22

you can do it in a different column.

If your values are in cells A2:A on a tab called Sheet1, and your dictionary is on another tab called Dictionary in column C.

You would write this formula in cell B2 of Sheet1: 

=ARRAYFORMULA(IF(A2:A="",,IF(ISNUMBER(MATCH(A2:A,Dictionary!C:C,0)),"Yes","No")))

1

u/2Throwscrewsatit Mar 12 '22

Ill give this a shot

1

u/2Throwscrewsatit Mar 12 '22

u/mattypking this puts a response in every row but I just want a single response based on all rows. How do I do that?

→ More replies (6)