Multiple nested excel formula

Question

I have this excel situation:

C2 = "A", D2= "A", set E2 = "A"

C2 = "B", D2 = "B", set E2 = "B"

C2= "B", D2= "A", set E2 = "A"

and....

How can I write such nested formulas for column E to be populated from values of same row column C and D?

We have a 5x5 value table. So if col = "X" and row = "Y" result should be "Z". I need to lookup this table and set value automatically with formula

Please advise

Generally speaking, in E2, you can do =If(And(C2="A",D2="A"),"A",if(And(C2="B",D2="B"),"B",...). but it looks like you have lots of these. A Vlookup() table might work too. Can you expand on your problem a little more? — BruceWayne
– BruceWayne, Commented Mar 18, 2016 at 20:42
@BruceWayne, updated the question. I tried the sample you sent it didn't work — user5393628
– user5393628, Commented Mar 18, 2016 at 20:45
Then as @BruceWayne just stated it would probably be better if you made a table with all the possible combinations and the desire output then use a vlookup or INDEX/MATCH. Nesting an indeterminate amount of IFs is a little daunting. — Scott Craner
– Scott Craner, Commented Mar 18, 2016 at 20:46

Tom Sharpe · Accepted Answer · 2016-03-19 10:58:52Z

1

If you actually have the values in a 2d table something like this (in a sheet called Lookup say):-

then you can use the table to get the values.

Either using row and column headers:-

=IFERROR(INDEX(Lookup!$B$2:$F$6,MATCH(C2,Lookup!$A$2:$A$6,0),MATCH(D2,Lookup!$B$1:$F$1,0)),"")

or just using the position of the letters in a string:-

=IFERROR(INDEX(Lookup!$B$2:$F$6,FIND(C2,"ABCDE"),FIND(D2,"ABCDE")),"")

answered Mar 19, 2016 at 10:33

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1