How do I create more than 7 nested If statements in Excel? 1

[markbanker]

I have a sheet that contains a list of books and their associated prices. On my other sheet you enter a bookID. This bookID is used to identify a book and find its price. I used a series of 7 nested if statements that look to the book sheet, find the appropriate book and return its price. So far, so good. The problem is...there are many more than 7 books. According to Excel Help, you can only nest 7 if statements. If I add more, it breaks.

So, my thought was to do the 7 nested ifs, and then an OR followed by a series of 7 nested ifs, etc. It won't accept the OR following the nested ifs.

The next thing I tried was to dynamically build a reference to the books sheet by concatenating the sheet reference and the bookID number, in this manner: ="Books!c"&B2. This seems that it would be the most efficient way to accomplish this task.

The problem with this is that the result of concatentation is a string object, rather than a reference to another sheet. It just displays the resultant string. I tried enclosing it in an eval() function, thinking that it would cause it to evaluate the string like the reference that it is, but no such luck. I tried several other things and all have failed.

I greatly appreciate all of you who take the time to consider this issue on my behalf.

Thanks,

Mark Banker

“Coffee should be black as hell, strong as death, and sweet as love.”
- Turkish proverb

 

[GlennUK]

According to your example, your Bookid number is the same as the row number of the information you require. Instead of using ="Books!c"&B2 use =INDIRECT("Books!c"&B2) and see if that does what you want.

Glenn.

 

[joerd]

You could also try a VLOOKUP function. Regards,

Joerd

 

[jode]

Or you could try a case statement

eg

Select Case bookno
Case 1
Do something
Case 2
Do something else
Case else
Do something completely different
End Select

 

[SkipVought]

What you ought to use is TWO nested for...next loops. Skip,
Skip@theofficeexperts.com

 

[markbanker]

Thank you for the responses! The VLOOKUP worked like a charm. Here's the code that worked: =VLOOKUP(B2,'Book Order Sheet.xls'!BookRange,3,FALSE).

In cell B2 I enter the book ID. In my Book Order Sheet workbook, but on a separate worksheet, I have a named range called BookRange. The formula does a VLOOKUP, or verticle lookup, based on four arguments: the value you are looking for(B2), where to look (in the named range, 'Book Order Sheet.xls'!BookRange'),from which column of the range to return a value(column 3, which contains the price), and what to do if you can't find the value you have searched for(b2). FALSE says to notify me that the value is not present by displaying #N/A.

I did not try the solutions posted after joerd's. Thank you joerd for this solution. Thank you GlennUK, vode, and SkipVought for your responses.

Mark Banker