Use IF/AND or INDEX/MATCH?

Jodie Kelley

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

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.

Jodie Kelley

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!