Nested IF statements

[danny_p85]

Hey guys,

(first time poster!)

I'm trying to write a nested IF statements, and I'm not sure if there is an AND or OR statement that should be added to complete the formula. My goal for example: IF D27=x,D28=y, return C9 OR IF D27=x,D28=z, return C10, and so on replacing D27 and D28 with 100+ other options, giving 100+ other returns

Thanks for the help,

Dan
Amateur Excel user

 

[WaterGypsy]

Sorry if I am stating the bl33din obvious ....

firstly you'd write =IF(AND(D27=x,D28=y),C9,IF(AND(D27=x,D28=z),C10, ........ ))

BUT that is going to be a very long formula and I may be wrong but I think there is a limit on nested formulae that is well under 100.

I think you may need a different solution

 

[WaterGypsy]

What I would do is setup a table (name it XREF_TAB for want of something better) and populate it with the combinations of D27 & D28 that you want to use in column 1 and then 9, 10, 11 etc to decide which row in column C to return .... then use this formula:

=OFFSET(C1,VLOOKUP(D27&D28,XREF_TAB,2,FALSE)-1,0)

 

[danny_p85]

Sorry if I am stating the bl33din obvious ....

firstly you'd write =IF(AND(D27=x,D28=y),C9,IF(AND(D27=x,D28=z),C10, ........ ))

BUT that is going to be a very long formula and I may be wrong but I think there is a limit on nested formulae that is well under 100.

I think you may need a different solution

Hi WaterGypsy, yes I've tried this, and it returns with a FALSE answer...

 

[WaterGypsy]

You probably get FALSE because there is no match in any of the conditions.... you'll need to put a 'catch all' of some sort at the end of the formula.

so the end of the formula will look something like:

.....if(AND(d27=p,28=r,C99,"No match found"))))))

 

[WaterGypsy]

ooopps ... that should be

.....if(AND(d27=p,28=r),C99,"No match found"))))))

 

[danny_p85]

ooopps ... that should be

.....if(AND(d27=p,28=r),C99,"No match found"))))))

Thanks! It's working