CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Deedmunkee
Deedmunkee

Reputation: 27

How to match one Text with a whole column? Google sheets

So some agency want to know how many users are allowed to get into their system they send me all users that are allowed, now i want match every user with their data, if the user exit in their column then write "TRUE" if not "FALSE"? Thank you.enter image description here

Names Agency1 Allowed?
user565 user44 FALSE
user4851 user4 TRUE
user548 user3 FALSE
user4 user884 FALSE

Upvotes: 0

Views: 83

Answers (3)

z..
z..

Reputation: 12823

Another alternative is to use COUNTIF.

=ArrayFormula(countif(A2:A5,B2:B5)>0)

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

use:

=INDEX(IFNA(MATCH(B2:B5; A2:A5; ))>0)

enter image description here

Upvotes: 2

Doug Moore
Doug Moore

Reputation: 1193

You should be able to accomplish this using MATCH. Something like:

MATCH(C2,B$2:B)

Note that I am using B$2 here. This means that when you copy this formula to each row, it will continue to look at all of the values in xolumb B. So this is only the formula for the first row.

If MATCH does not find the result, it will return #N/A. This can be handled with IFNA:

IFNA(MATCH(C2, B$2:B), FALSE)

This will return FALSE if the MATCH returns #N/A

Now, to get your TRUE/FALSE value, you need to do a comparison of the above:

IF(IFNA(MATCH(C2, B$2:B), FALSE), TRUE, FALSE)

Upvotes: 3

Related Questions