SOLVED

Nested IF AND formula for a table of values

%3CLINGO-SUB%20id%3D%22lingo-sub-2022983%22%20slang%3D%22en-US%22%3ENested%20IF%20AND%20formula%20for%20a%20table%20of%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2022983%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20on%20a%20nested%20IF%20AND%20formula.%20I%20have%20the%20first%20part%20correct%20with%20%3DIF(AND(B17%3DA1%2CB18%3DD2%2CB19%3D%22Monthly%22)%2CB3)%20where%20I%20have%20a%20separate%20table%20with%20values%20in%20A1%2C%20B2%20%26amp%3B%20B3.%20I%20have%20several%20more%20values%20in%20the%20table%20in%20different%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20is%20combine%20several%20IF%20AND%20formulas%20into%20one%20cell%20so%20that%20when%20I%20change%20the%20values%20of%20cells%20B17%2C%20B18%20%26amp%3B%20B19%2C%20the%20cell%20with%20the%20formula%20(D19)%20displays%20an%20answer%20from%20the%20table.%20If%20there%20is%20a%20better%20way%20to%20set%20this%20up%20than%20using%20nested%20IF%20AND%2C%20then%20please%20advise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20and%20Happy%20New%20Year!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2022983%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023145%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20formula%20for%20a%20table%20of%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914472%22%20target%3D%22_blank%22%3E%40NJ_Bruno%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20make%20your%20NESTED%20IF%20statement%20less%20complicated%20and%20more%20dynamic%2C%20it%20is%20best%20to%20unpivot%20your%20data%20and%20maintain%20one%20single%20table%20reference%20(as%20long%20as%20the%20same%20parameters%2Finformation%20are%20shared).%20Here's%20an%20example%20of%20what%20I%20mean%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_1-1609353607020.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243510iB473D41A73BC0E86%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_1-1609353607020.png%22%20alt%3D%22adversi_1-1609353607020.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20this%20new%20setup%2C%20your%20complex%20NESTED%20IF%20statement%20is%20no%20longer%20needed%20and%20a%20simple%20SUMIFS%20statement%20can%20be%20used%20to%20return%20the%20right%20Fee%20figure%20you're%20looking%20for%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023170%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20formula%20for%20a%20table%20of%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023170%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%26nbsp%3BExcellent!%20Thank%20you%20so%20much.%20I'll%20work%20with%20this%20and%20post%20again%20if%20I%20have%20any%20problems.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023445%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20formula%20for%20a%20table%20of%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023445%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914472%22%20target%3D%22_blank%22%3E%40NJ_Bruno%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENormalising%20the%20data%20into%20a%20table%20has%20a%20lot%20to%20commend%20it%2C%20but%20it%20is%20possible%20to%20work%20directly%20with%20data%20presented%20as%20arrays.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20FeeTable%2C%20IF(Binsize%3D35%2C%20Size35fees%2C%20Size64fees)%2C%0A%20%20Freq%2C%20XMATCH(Frequency%2C%20FrequencyOptions)%2C%0A%20%20Fee%2C%20INDEX(FeeTable%2C%20Freq%2C%20Quantity)%2C%0A%20%20Fee)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%2C%20in%20older%20versions%20of%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20INDEX(%20%0A%20%20%20%20IF(Binsize%3D35%2C%20Size35fees%2C%20Size64fees)%2C%20%0A%20%20%20%20MATCH(Frequency%2C%20FrequencyOptions%2C%200)%2C%20%0A%20%20%20%20Quantity)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2024782%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20formula%20for%20a%20table%20of%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BThanks%2C%20Peter.%20I%20like%20the%20drop%20down%20menu%20feature.%20I'll%20play%20around%20with%20both%20and%20come%20up%20with%20something%20that%20works%20well%20for%20our%20purposes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated%20and%20have%20a%20great%20New%20Year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Nick%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026529%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20AND%20formula%20for%20a%20table%20of%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914472%22%20target%3D%22_blank%22%3E%40NJ_Bruno%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24B%241%3A%24F%2415%2CMATCH(%24B%2417%2C%24A%241%3A%24A%2415%2C0)%2BMATCH(%24B%2419%2C%24A%241%3A%24A%2415%2C0)-1%2C%24B%2418)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Community!

 

I need help on a nested IF AND formula. I have the first part correct with =IF(AND(B17=A1,B18=D2,B19="Monthly"),B3) where I have a separate table with values in A1, B2 & B3. I have several more values in the table in different cells.

 

What I want to do is combine several IF AND formulas into one cell so that when I change the values of cells B17, B18 & B19, the cell with the formula (D19) displays an answer from the table. If there is a better way to set this up than using nested IF AND, then please advise.

 

Thanks and Happy New Year!

7 Replies
Best Response confirmed by NJ_Bruno (New Contributor)
Solution

@NJ_Bruno 

To make your NESTED IF statement less complicated and more dynamic, it is best to unpivot your data and maintain one single table reference (as long as the same parameters/information are shared). Here's an example of what I mean:

 

adversi_1-1609353607020.png

 

With this new setup, your complex NESTED IF statement is no longer needed and a simple SUMIFS statement can be used to return the right Fee figure you're looking for

@adversi Excellent! Thank you so much. I'll work with this and post again if I have any problems. 

 

Much appreciated!

@NJ_Bruno 

Normalising the data into a table has a lot to commend it, but it is possible to work directly with data presented as arrays.

 

 

= LET(
  FeeTable, IF(Binsize=35, Size35fees, Size64fees),
  Freq, XMATCH(Frequency, FrequencyOptions),
  Fee, INDEX(FeeTable, Freq, Quantity),
  Fee)

 

 

or, in older versions of Excel

 

 

= INDEX( 
    IF(Binsize=35, Size35fees, Size64fees), 
    MATCH(Frequency, FrequencyOptions, 0), 
    Quantity)

 

 

 

@Peter Bartholomew Thanks, Peter. I like the drop down menu feature. I'll play around with both and come up with something that works well for our purposes.

 

Much appreciated and have a great New Year.

 

-Nick

@NJ_Bruno 

As variant

=INDEX($B$1:$F$15,MATCH($B$17,$A$1:$A$15,0)+MATCH($B$19,$A$1:$A$15,0)-1,$B$18)

@Sergei Baklan Thank you! I like this because it allows me to keep the table in matrix form.

 

Much appreciated!

Nick

@NJ_Bruno , glad to help.