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.

Find more posts tagged with

Formulas

Comments

Paul Newcome

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?

j.green

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.

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…