Checking two sheets for a match and returning 'yes' or 'no'

greenes · 07/08/24

I have two reference sheets that I'm trying to connect to another sheet that will serve as a log. How it works: User enters information (VTH Code is associated with a vendor price on both the reference sheets). The user enters the price a client was charged. This is used to check if it matches either of the reference sheets and returns Yes or No to a new column.

I have successfully linked one sheet:

=IF(COUNTIFS({Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row), {Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row)) > 0, "Yes", "No")

This works! Hurray!

But I'm struggling to get the other sheet connected so it checks both. I've tried both below:

=IF(COUNTIFS({Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row), {Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row)) > 0, "Yes", "No"), OR({Outside Lab Tests - Large Animal Range 2}, CONTAINS(@cell, [VTH Code]@row), {Outside Lab Tests - Large Animal Range 1}, HAS(@cell, [Outside Lab Charges]@row)) > 0, "Yes", "No")

=IF(COUNTIFS(IF(COUNTIFS({Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row), {Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row))), {Outside Lab Tests - Large Animal Range 2}, CONTAINS(@cell[VTH Code]@row), {Outside Lab Tests - Large Animal Range 1}, HAS(@cell, [Outside Lab Charges]@row))) > 0, "Yes", "No")

I feel like I'm close but can't quite figure it out. Thanks for any help pointing me in the right direction!

heyjay · 07/09/24

https://community.smartsheet.com/discussion/comment/444210#Comment_444210

How bout this one?


=IF(

COUNTIFS(
{Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row),
{Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row)) +

COUNTIFS(
{Outside Lab Tests - Large Animal Range 2}, CONTAINS(@cell, [VTH Code]@row),
{Outside Lab Tests - Large Animal Range 1}, HAS(@cell, [Outside Lab Charges]@row)) 

> 0, "Yes", "No")

SoS | Dan Palenchar · 07/09/24

Hello @greenes,

If you have the two formulas working correctly individually I would try to structure it like this where you simply add the two formulas together and check if the combined value is greater than 0:

=IF({COUNTIFS Formula 1} + {COUNTIFS Formula 2} > 0, "Yes", "No")

Hope that helps!

greenes · 07/09/24

https://community.smartsheet.com/discussion/comment/444031#Comment_444031

Hi, thanks for the help! This is what I have but I'm getting a syntax error:

=IF(COUNTIFS({Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row), {Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row)) + ({Outside Lab Tests - Large Animal Range 2}, CONTAINS(@cell, [VTH Code]@row), {Outside Lab Tests - Large Animal Range 1}, HAS(@cell, [Outside Lab Charges]@row)) > 0, "Yes", "No")

Am I missing something?

Thanks!

SoS | Dan Palenchar · 07/09/24

Hello @greenes,

What is the specific error you are getting?

greenes · 07/09/24

"The column formula syntax isn't quite right" @Dan Palenchar

heyjay · 07/09/24

Looks like you are missing a COUNTIFS function after the plus sign:

=IF(

COUNTIFS(

{Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row),

{Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row)) +

COUNTIFS(

({Outside Lab Tests - Large Animal Range 2}, CONTAINS(@cell, [VTH Code]@row),

{Outside Lab Tests - Large Animal Range 1}, HAS(@cell, [Outside Lab Charges]@row)) 

> 0, "Yes", "No")

Dont trust my formula ;) but try to disect yours. This is just a sample.

greenes · 07/09/24

https://community.smartsheet.com/discussion/comment/444128#Comment_444128

Thanks @heyjay I think I'm getting closer. Now returning an Incorrect Argument error.

heyjay · 07/09/24

https://community.smartsheet.com/discussion/comment/444210#Comment_444210

How bout this one?


=IF(

COUNTIFS(
{Outside Lab Tests - Small Animal Range 1}, CONTAINS(@cell, [VTH Code]@row),
{Outside Lab Tests - Small Animal Range 3}, HAS(@cell, [Outside Lab Charges]@row)) +

COUNTIFS(
{Outside Lab Tests - Large Animal Range 2}, CONTAINS(@cell, [VTH Code]@row),
{Outside Lab Tests - Large Animal Range 1}, HAS(@cell, [Outside Lab Charges]@row)) 

> 0, "Yes", "No")

greenes · 07/09/24

https://community.smartsheet.com/discussion/comment/444212#Comment_444212

@heyjay You are my absolute hero! This made it really easy to see every part of the formula and it indeed works. Thank you VERY much for your assistance, I truly appreciate it!

Julie Becker · 07/10/24

Sorry if this is off topic guys, but i would like to know @heyjay if you are using a program to decifer the detail breakdows as you are showing in this img below?

Basically i am wanting to know if you are using a free program that points out the COUNTIF / COUNTIFS, HAS, etc… as you kinda did with colors so that those of us who need to break large formulas down, can do so with the colors/boldness to quickly identify the missing pieces. One of my schedulers mentioned a C++ type program, but it seemed like a lot of work to set it up, i just want something like a text program that can point out the missing formula items that are typical/required in those types of formulas entered.

➡️ By the way, im so ❤️ glad @greenes you got your formula working ⬅️ 😁

heyjay · 07/10/24

Everything is just based on experience and error codes you mentioned, plus the awesome documentation here. Say like, Incorrect Argument error means misplaced comma usually, Invalid Reference means your reference is incorrect.

With regards to highlighting and colors, its built in here in the forum. You can also try Notepad++ or Sublime Text editor.

For the formatting, I just break them by chunks on how I can easily understand each formula. If its nested, create line per nest to easily read and debug.

Checking two sheets for a match and returning 'yes' or 'no'

Best Answer

Answers

Help Article Resources

Categories