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,Sh eet2!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.

Shorten an IF formula
 

=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)),"",VL OOKUP(C11,Sheet2!C1:E91,3,FALSE))

--
Kind regards,

Niek Otten

"Joker" wrote in message
...
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,Sh eet2!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.

Shorten an IF formula
 

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" wrote in message
...
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,Sh eet2!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.

Shorten an IF formula
 

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)),"",VL OOKUP(C11,Sheet2!C1:E91,3,FALSE))

--
Kind regards,

Niek Otten

"Joker" wrote in message
...
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,Sh eet2!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.

Shorten an IF formula
 

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" wrote in message
...
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,Sh eet2!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.