IF/AND/HAS for 3 column references, 6 options
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 TaxPaid, Bottling LOCATION is Infinity Bottling, Entity is any of the 6, it enters the corresponding number from that entity into the TAXPAID BOTTLING HOUSE PERMITS cell. I've created 2 reference formulas before, but this one is outside my ability. Appreciate any assistance.
Best Answer

HI @MMMueffelman,
Is something like this what you're after?
=IF(AND([Tax Status]@row = "TaxPaid", [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 = "TaxPaid", [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! 🙂
Answers

HI @MMMueffelman,
Is something like this what you're after?
=IF(AND([Tax Status]@row = "TaxPaid", [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 = "TaxPaid", [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! 🙂

Perfect, thank you! Nesting formulas get me every time. 😁
Help Article Resources
Categories
Check out the Formula Handbook template!