IF auto generated number in Row matches anywhere in 2 sheet columns then checkbox checked

Wes S

Hello, 

I am trying to input a formula that automatically checks a checkbox when an auto generated number from a specific row in one sheet, is found anywhere in a column from 2 separate sheets. 

I've tried to make it work by referencing just one of the two sheets but it is behaving strangely. 

 

The formula I tried is =IF(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 1, 0)

If I drag the formula down the checkbox rows and populate the referenced sheet with matching Lead ID's I get a checked box on my first row and INVALID DATA TYPE on any others that should match. 

Thanks in advance for any help you're able to give.

Paul Newcome

The MATCH function actually produces a number based on where within a grid (range) the data is found. Your current formula is basically

 

=IF(##, 1, 0)

 

You don't really have a logical statement. What you would ideally have would be along the lines of

 

=IF(## > 0, 1, 0)

 

When we replace ## with your MATCH formula it is basically saying that if the data is found in any position within the grid (range), check the box.

 

So here would be a minor adjustment to your current formula that should work. Let me know if it does.

 

=IF(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0) > 0, 1, 0)

Wes S

Hi Paul, 

 

That worked! If it isn't a match it returns #NOMATCH rather than leaving the checkbox unchecked. I can live with that but is there a way to preserve the unchecked checkbox?

Also, fairly new to formulas and wondering how I then add the other sheet to the MATCH range. 

Thank you so much. 

Wes

Paul Newcome

=IF(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0) > 0, 1, 0)

 

To avoid the #NO MATCH error, we wrap the MATCH function in an IFERROR statement to get it to return the number 0. Since that is not greater than 0, it will leave the box unchecked.

=IF(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, 1, 0)

.

To account for a second sheet, we would simply use an OR statement and duplicate the MATCH function with the exception of the range (we will wrap that one in an IFERROR as well). You will need to follow the appropriate cross sheet referencing steps for the second sheet range.

 

=IF(OR(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]1, {Second Sheet Range 1}, 0), 0) > 0), 1, 0)

 

How does this work out for you?

Wes S

Paul, you're amazing. Thank you very much for such a thorough response. It works like a charm and I understand how it's all functioning. 

Is it just me or is this stuff a tonne of fun? 

Thanks again, 

Wes

Paul Newcome

Happy to help! 

 

Is this stuff fun...? Give it time. Hahahaha

Wes S

I have one more addition that I'm trying to make to this formula. I'm trying to also add- IF column = "Declined" and a date column has any date in it = checkbox checked. 

This is my current attempt. 

=IF(OR(IFERROR(MATCH([Lead ID]2, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]2, {Maintenance Estimate Worksheet Range 1}, 0), 0) > 0), 1, IF(AND(IFERROR(MATCH("Declined", [Dave approval]2:[Wes Shelley approval]2, 0), 0), [Connected with on:]2 > 0), 1, 0))

Any help is much appreciated.

Paul Newcome

So you want the previous solution's criteria OR the word "Decline" in x-range OR a date in y-range?

 

It could be any one of those with equal importance meaning it won't necessarily look in a specific order, it just needs to find any one of those things?

Wes S

It would be the previous solutions criteria OR the word "Declined" in x range AND a date in y range.

Paul Newcome

Sorry. I just want to make sure we get this right...

 

Previous Solution

 

OR

 

Date and "Declined" together

.

Correct?

Wes S

Yes that's correct!

Paul Newcome

Perfect. Now for (hopefully) the last question...

 

Date and Declined... Are they on the same sheet as the formula, or are they going to be a cross sheet reference?

 

If x-sheet reference... Is there a way to uniquely identify the corresponding row (lead id/row id/etc)?

Wes S

Date and Declined both exist on the same sheet as the formula on the row that the formula is also on. 

Thanks so much Paul.

Paul Newcome

Phew. This makes things a lot easier. Hahaha. Give this a whirl...

 

=IF(OR(OR(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]1, {Second Sheet Range 1}, 0), 0) > 0), AND(ISDATE([Date Column]@row), [Other Column Name]@row = "Declined")), 1)

.

Basically what we are doing is

 

=IF(OR(Previous Solution's Criteria, AND(Date, "Declined")), check the box)

Wes S

It seems I'm getting an invalid operation error. 

 

=IF(OR(IFERROR(MATCH([Lead ID]1, {Construction/Enhancements Estimate Workshe Range 1}, 0), 0) > 0, IFERROR(MATCH([Lead ID]1, {Maintenance Estimate Worksheet Range 1}, 0), 0) > 0, AND(ISDATE([Connected with on:]@row), [Dave approval]1:[Wes Shelley approval]@row = "Declined")), 1)

Wes S

I've tracked it down to the problem being [Dave approval]@row:[Wes Shelley approval]@row = "Declined" portion.