Use IF/AND or INDEX/MATCH?

I have a sheet (top) where Site Code and Sub-GL are drop downs. There are hundreds of corresponding site/sub-GLs listed in my reference sheet (bottom).

I am looking for the correct formula to return value of "2021 OP2 Total" when Site Code and Sub-GLs are selected match a line in the reference sheet.

I can't seem to make a formula that works, and keep looping around between trying to use a nested IF/AND or INDEX/MATCH. I see that Vlookup would be a horrible idea for so many combinations so I'm trying to avoid that to prevent broken conditions.

Thank you for your help

Jodie Kelley - Sys Admin, Hines

[email protected]

2020 Smartsheet Product Certification

Tags:

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Jodie Kelley

    That's a problem I had to solve some times ago.

    The best solutions I had was to had an helper column on both sheets (that you'll be able to hide right after).

    In these helper columns use this formula:

    =JOIN([Site Code]@row:[Sub-GL]@row)

    This will combine both cells into a unique value

    Then you can do an INDEX/MATCH easily in your 2021 OP2 Total comparing those unique Values.

    Hope it helped.

  • Thank you for taking the time to respond, that helped me.

    It took me a bit to also Index/Match (i'm pretty new to formulas) but finally found success. Those helper columns come in handy. Thank you for your generosity!

    Jodie Kelley - Sys Admin, Hines

    [email protected]

    2020 Smartsheet Product Certification

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!