Multiple nested IF(AND) paired with INDEX/MATCH ? or other way ?

Hello to everyone.

Im starting to go deeper into advanced formulas and sheet reference, but have some issue...

I have a "customer activity" sheet, where for each line, there is 2 sets of variables (5 in "activity", 3 in "types"...) and a "Duration" column.

I would like to compute the "Final Price", by referencing a "price matrix", which contains 15 columns according to "activity/types" variables, and the Price for each "Durarion":

What would be the best approach for what i am trying to achieve ?

I was thinking of multiples IF(AND) conditions (15...) paired with INDEX(match) formulaes referencing each time a different column.....all nested... would this work ?

Could INDEX(Collect) be my solution ? I don't really understand how to formulate this one..

Any help greatly appreciated. Many Thanks by advance !

Regards.

Find more posts tagged with

Formulas

collect

INDEX

MATCH

Accepted answers

Paul Newcome

I would suggest a helper row across the top of your matrix that basically replicates your column names. Then a text/number helper column in the sheet where the formula lives that will combine the Activity and Type into a string that can be used to match across the top row of your reference table.

Then the formula for the lookup would be

=INDEX({Reference To Entire Table}, MATCH(Duration@row, {Duration Column}, 0), MATCH([Helper Column]@row, {Top Row}, 0))

Paul Newcome

@Brandon Sills I would use the helper row across the top of the table to allow for a much shorter and easier to manager INDEX/MATCH/MATCH instead of a large nested IF.

If you prefer the nested, IF, I would suggest using it to only output the appropriate column's cross sheet reference within the INDEX function so that you don't have to keep repeating the MATCH.

=INDEX(IF(This@row = "A", {Column A}, IF(This@row = "B", {Column B}, {Column C})), MATCH(Duration@row, {Duration}, 0))

All comments

Paul Newcome

Then the formula for the lookup would be

=INDEX({Reference To Entire Table}, MATCH(Duration@row, {Duration Column}, 0), MATCH([Helper Column]@row, {Top Row}, 0))

Paul Newcome

@Brandon Sills I would use the helper row across the top of the table to allow for a much shorter and easier to manager INDEX/MATCH/MATCH instead of a large nested IF.

If you prefer the nested, IF, I would suggest using it to only output the appropriate column's cross sheet reference within the INDEX function so that you don't have to keep repeating the MATCH.

=INDEX(IF(This@row = "A", {Column A}, IF(This@row = "B", {Column B}, {Column C})), MATCH(Duration@row, {Duration}, 0))

Jerome.m

Thank you Gentlemen !

I was successful with the nested IF(AND) functions, referencing my Matrix !

Thanks Paul for that Helper row tips and the simplified way of INDEXing/MATCHing, I will give it a try .

Best regards !

Paul Newcome

Happy to help. 👍️

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.