IF/AND/HAS for 3 column references, 6 options

MMMueffelman

Based on the content above for reference only, the actual sheet screenshot below. I need a formula or help with a helper sheet/workflow, to bring back one of 6 results.

(above image) Tax Status Column is a dropdown with the 2 options shown, Bottling Location and Entity Columns are INDEX(COLLECT formulas (if it matters) pulling from another sheet.

(Actual Below) If Tax Status is Tax-Paid, Bottling LOCATION is Infinity Bottling, Entity is any of the 6, it enters the corresponding number from that entity into the TAX-PAID BOTTLING HOUSE PERMITS cell. I've created 2 reference formulas before, but this one is outside my ability. Appreciate any assistance.

Nick Korna

HI @MMMueffelman,

Is something like this what you're after?

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1))

If you just wanted the number rather than whole string, you can use a RIGHT function in addition if the strings are uniform in length for the permit number. For example:

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), RIGHT(INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1), 5))

Would display the 5 digit number only.

Example output showing each formula in action:

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

Nick Korna

HI @MMMueffelman,

Is something like this what you're after?

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1))

If you just wanted the number rather than whole string, you can use a RIGHT function in addition if the strings are uniform in length for the permit number. For example:

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), RIGHT(INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1), 5))

Would display the 5 digit number only.

Example output showing each formula in action:

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

MMMueffelman

Perfect, thank you! Nesting formulas get me every time. 😁