Nesting multiple IF Statements

  • Thread starter Thread starter Scott Bowden
  • Start date Start date
S

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?
 
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.
 
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=""?
 
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.
 
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?
 
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.
 
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.
 
Back
Top