Nesting Limit Hit Using IF and AND functions

HomeTek

New Member
Joined
Jan 27, 2017
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
A lookup table in A1:B10?

Yes|No|No|YesResult 1
Yes|No|No|NoResult 2
Yes|No||YesResult 3
Yes|No||NoResult 4
Yes|No|Yes|NoResult 5
Yes|Yes|No|YesResult 6
Yes|Yes|No|NoResult 7

<colgroup><col><col></colgroup><tbody>
</tbody>

Then a vlookup:

=IFERROR(VLOOKUP(B16&"|"&B19&"|"&B22&"|"&B25,$A$1:$B$10,2,0),"")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top