Nesting multiple IF Statements

[Scott Bowden]

I am trying to create a spreadsheet that looks at a
particular set of cells for a date and then a meal
(Breakfast, lunch, or dinner) and returns a value only if
something is entered for the dollar amount in the
particular field.

What I have for days 1-6 works, but I need to get days 7-
12 in the same function. Excel Help says that you can only
nest 7 IF statements. Is there any way around that? Here
is what I have so far that works:

=IF(A20=""," ",VLOOKUP(F20,IF(A20=Day1,Day1range,IF
(A20=Day2,Day2range,IF(A20=Day3,Day3range,IF
(A20=Day4,Day4range,IF(A20=Day5,Day5range,IF
(A20=Day6,Day6range)))))),5,FALSE))

A20 is the date on sheet1. F20 is the meal (breakfast,
lunch, or dinner) on sheet1. Each Day is defined as a
group of cells on another sheet. Column Number 5 is the
dollar amount for that meal on that particular date.

How do I get it to search through days 7 through 12?

 

[Leo Heuser]

Scott

One way, assuming the numbers 1,2,3,...12
in A20

=IF(A20=""," ",VLOOKUP(F20,CHOOSE(A20,Day1range,Day2range,
Day3range,Day4range,Day5range,Day6range),5,FALSE)

Extend to Day12range.

 

[Paul]

Alternatively:
=IF(A20=""," ",VLOOKUP(F20,INDIRECT("Day"&A20&"Range"),5,FALSE))

By the way, why are you putting a space (" ") rather than nothing ("") if
A20=""?

 

[Scott Bowden]

Does this only work if the date in A20 is a number (i.e.
1,2,3,...)? Will it work if there is a date (i.e.
12/11/03) there? I tried this with a date and it doesn't
work.

 

[Peo Sjoblom]

=DAY(A20)

will return the day as in 1, 2 etc

 

[Scott Bowden]

This works if I put "1" or "2" in the date field, but is
there anyway to allow me to enter "12/11/2003" in that
field and it still return my results?

 

[Leo Heuser]

Yes, the solution will only work with
numbers 1,2,3,...12,

What dates can exist in A20?
If only dates 1 through 12 for each
month is possible, you can instead use:

=IF(A20=""," ",VLOOKUP(F20,CHOOSE(DAY(A20),Day1range,Day2range,
Day3range,Day4range,Day5range,Day6range),5,FALSE)

If you have named the day ranges as stated, Paul's
solution is the better choice.

 

[Paul]

It's not clear to me what you want. Someone else already pointed out that
the DAY function will return the day number from a date (i.e. a number in
the range 1 to 31), but you have only talked of 12 days.