Shorten an IF formula

Is there anyway to shorten an IF formula? What I am trying to do is display a
certain number that coinsides with the date. Here is the formula that I am
currently using. It works but it is too long and the "Formula Is Too Long"
error pops up when I have too many days.
=IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sheet2!E2,0)+IF(C11=Sheet2!C3,Sheet2!E3,0).
In the first part of the formula C11 is today's date. Sheet2!C1 is any date.
Sheet2!E1 is a number. I need a the cell to display the number if today's
date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank you
for your help.

=VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)

Or, if you don't like the #NA if there is no matching date,

=IF(ISNA(VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)),"",VLOOKUP(C11,Sheet2!C1:E91,3,FALSE))

--
Kind regards,

Niek Otten

"Joker" <[email protected]> wrote in message
news:[email protected]...
> Is there anyway to shorten an IF formula? What I am trying to do is
> display a
> certain number that coinsides with the date. Here is the formula that I am
> currently using. It works but it is too long and the "Formula Is Too Long"
> error pops up when I have too many days.
> =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sheet2!E2,0)+IF(C11=Sheet2!C3,Sheet2!E3,0).
> In the first part of the formula C11 is today's date. Sheet2!C1 is any
> date.
> Sheet2!E1 is a number. I need a the cell to display the number if today's
> date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank
> you
> for your help.

I cannot understand why you get an error with that, but this is shorter

=(C11=Sheet2!C1)*Sheet2!E1+(C11=Sheet2!C2)*Sheet2!E2+(C11=Sheet2!C3)*Sheet2!
E3

and if C1:C3 are uniique values, this is even shorter

=INDEX(Sheet2!E1:E3,MATCH(C11,Sheet2!C1:C3,0))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joker" <[email protected]> wrote in message
news:[email protected]...
> Is there anyway to shorten an IF formula? What I am trying to do is
display a
> certain number that coinsides with the date. Here is the formula that I am
> currently using. It works but it is too long and the "Formula Is Too Long"
> error pops up when I have too many days.
>
=IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sheet2!E2,0)+IF(C11=Sheet2!C
3,Sheet2!E3,0).
> In the first part of the formula C11 is today's date. Sheet2!C1 is any
date.
> Sheet2!E1 is a number. I need a the cell to display the number if today's
> date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank
you
> for your help.

Thank you so much Niek. That worked perfectly. I really appreciate the help
you guys provide.

"Niek Otten" wrote:

> =VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)
>
> Or, if you don't like the #NA if there is no matching date,
>
> =IF(ISNA(VLOOKUP(C11,Sheet2!C1:E91,3,FALSE)),"",VLOOKUP(C11,Sheet2!C1:E91,3,FALSE))
>
> --
> Kind regards,
>
> Niek Otten
>
> "Joker" <[email protected]> wrote in message
> news:[email protected]...
> > Is there anyway to shorten an IF formula? What I am trying to do is
> > display a
> > certain number that coinsides with the date. Here is the formula that I am
> > currently using. It works but it is too long and the "Formula Is Too Long"
> > error pops up when I have too many days.
> > =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sheet2!E2,0)+IF(C11=Sheet2!C3,Sheet2!E3,0).
> > In the first part of the formula C11 is today's date. Sheet2!C1 is any
> > date.
> > Sheet2!E1 is a number. I need a the cell to display the number if today's
> > date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank
> > you
> > for your help.
>
>
>

Thanks for the reply Bob. The reason I was getting the error is because I was
using each day as another IF formula. I had 91 days so I had 91 formulas
added together. I only included the first three in my post. Excel apparently
only lets you have 1240 or so characters. That is the reason for the error. I
really appreciate the quick reply. Thank you.

"Bob Phillips" wrote:

> I cannot understand why you get an error with that, but this is shorter
>
> =(C11=Sheet2!C1)*Sheet2!E1+(C11=Sheet2!C2)*Sheet2!E2+(C11=Sheet2!C3)*Sheet2!
> E3
>
> and if C1:C3 are uniique values, this is even shorter
>
> =INDEX(Sheet2!E1:E3,MATCH(C11,Sheet2!C1:C3,0))
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Joker" <[email protected]> wrote in message
> news:[email protected]...
> > Is there anyway to shorten an IF formula? What I am trying to do is
> display a
> > certain number that coinsides with the date. Here is the formula that I am
> > currently using. It works but it is too long and the "Formula Is Too Long"
> > error pops up when I have too many days.
> >
> =IF(C11=Sheet2!C1,Sheet2!E1,0)+IF(C11=Sheet2!C2,Sheet2!E2,0)+IF(C11=Sheet2!C
> 3,Sheet2!E3,0).
> > In the first part of the formula C11 is today's date. Sheet2!C1 is any
> date.
> > Sheet2!E1 is a number. I need a the cell to display the number if today's
> > date matches the date on the Sheet2. Sheet2 has 91 days to display. Thank
> you
> > for your help.
>
>
>