Multiple sheets with If AND function

Options

I'm needing assistance, trying to have the formula for the cell first look in a sheet for a check box, then look back in the original sheet for an AND value, then for the true statement do an Index, Match. I have tries multiple combinations but can figure it out, here is what I currently have:

=IFERROR(IF(AND({Study Device Inventory by Country Range 3} = "True", [# Tablets]@row > 0), INDEX({Study Device Inventory by Country Range 1}, MATCH(Country@row, {Study Device Inventory by Country Range 2}, 0)), "-"))

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 12/08/23
    Options

    @dharberts are you getting an error with this formula, or is it not getting the data that you need it to get?

    To help with the troubleshoot process, you can try to set a false condition, and a separate IFERROR Function

    =IFERROR(IF(AND({Study Device Inventory by Country Range 3} = "True", [# Tablets]@row > 0), 
    INDEX({Study Device Inventory by Country Range 1}, 
    MATCH(Country@row, {Study Device Inventory by Country Range 2}, 0)), "-"), "No Data Found")
    
    Sincerely,

    Jacob Stey

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Options

    Hi,

    What are you currently getting as a result? I usually use a 1 or 0 for checkbox results, but I know it essentially is just translating it to a True value. I would try changing it though, just to be sure.

    • =IFERROR(IF(AND({Study Device Inventory by Country Range 3} = 1, [# Tablets]@row > 0), INDEX({Study Device Inventory by Country Range 1}, MATCH(Country@row, {Study Device Inventory by Country Range 2}, 0)), "-"))

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • dharberts
    dharberts ✭✭✭
    Options

    After making the adjustment I get the same response:

    #INCORRECT ARGUMENT SET

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!