Match Formula with another sheet
I am having difficulty with this formula that I would appreciate some assistance.
I have a sheet with user names. If the user name matches that of a user name in another sheet, I need to select the checkbox.
I have a column with a checkbox and the following formula: =IF(MATCH(Name1, {2019-01-11 IS Bi-Weekly Report Range 2}, 0), 1, 0)
I have 1 checkbox checked and 600 either #NO MATCH or #INVALID DATA TYPE.
Comments
-
MATCH will pull a number value based on a location within a range. So let's just assume that the MATCH function is working as it should and the result is 1. Your IF statement would read as this
=IF(1, 1, 0)
There is a missing part to your logical expression portion of your IF statement.
I think that the below should work rather well for your needs.
=IF(COUNTIFS({2019-01-11 IS Bi-Weekly Report Range 2}, FIND(Name@row, @cell) > 0, 1, 0)
What this does is counts looks at your range and counts how many times it finds that particular name. If the count is greater than zero meaning there is at least one cell that matches that name within the designated range, it will check your box. Otherwise it will leave it blank.
Does that work for you?
-
Thank you for the information, Paul. I still was unable to get it to work, but you lead me to my solution:
=(COUNTIF({2019-01-11 IS Bi-Weekly Report Range 1}, Name1))
Success!!!
Thank you again.
J -
Excellent. Happy to help.
The FIND portion of my solution is something I have gotten into the habit of doing because it will even count the cells where that name is part of a list within a cell.
Glad you were able to find a solution though. Some of SS's functions seem counter-intuitive at times such as MATCH. You would think it would return true/false, but in reality it returns a row number.
Help Article Resources
Categories
Check out the Formula Handbook template!
