Solved

# Excel 2010 Formula constraint

Posted on 2014-04-10
Medium Priority
266 Views
We have an Excel Formula that we want function to expand the rows and columns to has much as possible but we are not sure how to do that. How do we set it as long as possible?

The formula/function is:
=IF(F5=AR5," ",IF(F5=AS5,AT5,IF(F5=AW5,AX5,IF(F5=BA5,BB5,IF(F5=BE5,BF5,IF(F5=BI5,BJ5,IF(F5=BM5,BN5,IF(F5=BQ5,BR5))))))))

Any assistance offered would be appreciated.
0
Question by:regsamp
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 13

Expert Comment

ID: 39992174
we want function to expand the rows and columns to has much as possible but we are not sure how to do that. How do we set it as long as possible

not getting, pls explain it, as the given formula is for different purpose.
0

Author Comment

ID: 39992229
It is hard to explain but I can try. In the formula it is taking a revision date from one cell and putting it in another and if it is not in the A cell then it goes to the B and further to check but we are getting an error message when we try to add more options to the formula. I can post the error message and document if it will help.
Capture.JPG
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39992362
Perhaps try using INDEX/MATCH, you can expand this as far as required

=IF(F5=AR5,"",INDEX(AT5:BR5,MATCH(F5,AS5:BQ5,0)))

regards, barry
0

Author Comment

ID: 39992407
Thank you for the INDEX/MATCH option. I am going to try that with the formula to get beyond the NEST issue.
0

Author Closing Comment

ID: 39992427
The INDEX/MATCH option. I apologize for the duplicate/confusion on the formula. I did not post the NEST error message and it would have saved time. My fault.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Progress
###### Suggested Courses
Course of the Month14 days, left to enroll