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

Find more posts tagged with

Formulas

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.