IF CONTAINS Issue

Chris Benskin

I have a formula in my Purchasing Log sheet that will look at the Vendor Name and compare it to Supplier Name in the Approved Supplier List and if the Vendor is in the Approved Supplier List will fill a check box. It works well but I want to add another condition to only check the box if the Supplier Status Column in the Approved Supplier List contains "Approved".

The formula needs updating since I now have vendors on the Approved Supplier List that have been Inactivated. The way the formula currently sits, this check box will be marked even if the vendor has been made inactive. I don't want that to happen.

Here is my current formula

=IF(CONTAINS(Vendor@row, {Approved Supplier List Range 1}), 1)

Thank you in advance for any help.

Kelly Moore

Hey Chris

Do both of the 'Approved' conditions contain the word 'Approve'? If yes, you can also write it as a CONTAINS. Recall that the CONTAINS function will pick up any variation of the search term.

We can use an OR if needed. However, if the CONTAINS solves it, since you're already familiar with that function, that would be the way I would go.

=IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, CONTAINS("Approve", @cell))>0, 1)

Kelly Moore

Hey Chris

=IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, "Approved")>0, 1)

This says if the two conditions are found (which means the count will be more than zero), then check the box.

Remember since this is a cross sheet reference, you cannot simply cut and paste - you must build the cross sheet reference manually.

Kelly

Chris Benskin

Kelly

Formula works fantastic. How would I account for Conditionally Approved in the {Approved Supplier List Supplier Status} along with Approved? Is there an OR statement in the formula or would it be adding another CONDITION statement in the formula? I've tried some various ideas but to no avail.

Kelly Moore

Hey Chris

Do both of the 'Approved' conditions contain the word 'Approve'? If yes, you can also write it as a CONTAINS. Recall that the CONTAINS function will pick up any variation of the search term.

We can use an OR if needed. However, if the CONTAINS solves it, since you're already familiar with that function, that would be the way I would go.

=IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, CONTAINS("Approve", @cell))>0, 1)

Chris Benskin

Kelly

Using CONTAINS worked perfect. Thank you.

During this endeavor I also discovered making formulas into column formulas. This should alleviate many issues experienced by our organization.

Kelly Moore

Glad it worked, and yes, try to use column formulas whenever possible.

cheers