Nesting Limit Hit Using IF and AND functions

[HomeTek]

Hi all,

Using Excel 2007

I have Googled and Googled and tried to use various examples to help me with this but to no avail.

I know nesting isn't ideal, but I need to possibly nest around 10 statements. However I have hit the limit of 7 and can no longer add any more conditions.

Below is an example of the formula I have been using.

=IF(AND(B16="Yes",B19="No",B22="No",B25="Yes"),"Result 1",IF(AND(B16="Yes",B19="No",B22="No",B25="No"),"Result 2",IF(AND(B16="Yes",B19="No",B22="",B25="Yes"),"Result 3",IF(AND(B16="Yes",B19="No",B22="",B25="No"),"Result 4",IF(AND(B16="Yes",B19="No",B22="Yes",B25="No"),"Result 5",IF(AND(B16="Yes",B19="Yes",B22="No",B25="Yes"),"Result 6",IF(AND(B16="Yes",B19="Yes",B22="No",B25="No"),"Result 7","")))))))

If I was using a later Excel I would have no problems as the limit is higher and I only need around 10 or so nesting levels. But unfortunately I am using Excel 2007 and have hit the limit.

Does anyone know how I could amend the formula above at all to get around this problem or maybe I need to totally scrap it and look at it from a different angle?

Many thanks

 

[steve the fish]

A lookup table in A1:B10?

[TABLE="width: 235"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Yes|No|No|Yes[/TD]
[TD]Result 1[/TD]
[/TR]
[TR]
[TD]Yes|No|No|No[/TD]
[TD]Result 2[/TD]
[/TR]
[TR]
[TD]Yes|No||Yes[/TD]
[TD]Result 3[/TD]
[/TR]
[TR]
[TD]Yes|No||No[/TD]
[TD]Result 4[/TD]
[/TR]
[TR]
[TD]Yes|No|Yes|No[/TD]
[TD]Result 5[/TD]
[/TR]
[TR]
[TD]Yes|Yes|No|Yes[/TD]
[TD]Result 6[/TD]
[/TR]
[TR]
[TD]Yes|Yes|No|No[/TD]
[TD]Result 7[/TD]
[/TR]
</tbody>[/TABLE]

Then a vlookup:

=IFERROR(VLOOKUP(B16&"|"&B19&"|"&B22&"|"&B25,$A$1:$B$10,2,0),"")

 

[HomeTek]

Brilliant. It works perfectly.

I did try to use a Vlookup originally, but I had no idea you could do a Vlookup using multiple cells like that until now.

Much appreciated.