need help with lookup formula
 

i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

need help with lookup formula
 

Try like this:

=LOOKUP(E45,{0,54.......},{4,5........}


"Gil" wrote:

i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

need help with lookup formula
 

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{4,5,6,7,8,9,10,11})
You'd need to have the same number of elements
in the two array sections of your formula.

Perhaps like this:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{4,5,6,7,8,9,10,11,12,13,14,15})

But maybe this will work for you:
=MAX(FLOOR((E45-5)/12,1),4)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Gil" wrote in message
...
i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

need help with lookup formula
 

On Jan 31, 10:50*am, Gil wrote:
i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. *if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A


Don't know if your values are examples, but if not...this should work:
=MAX(4,(((E45-5)-MOD(E45-5,12))/12))

hth
Carlo

need help with lookup formula
 

On Jan 30, 9:15*pm, "Ron Coderre"
wrote:

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{4,5,6,7,8,9,10,11})
You'd need to have the same number of elements
in the two array sections of your formula.

Perhaps like this:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{4,5,6,7,8,9,10,11,12 ,13,14,15})

But maybe this will work for you:
=MAX(FLOOR((E45-5)/12,1),4)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Gil" wrote in message

...

i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. *if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

thank you,

both of the previous posts seem to do the trick....i am still trying
to figure out this forum...i posted a reply to author, but don't see
it up on the forum.

the addition of the 0 in the lookup formula before the 53 did the
trick and so did the other formula of =MAX(4,(((E45-5)-MOD(E45-5,12))/
12).

i don't understand what the second formula does, but i hope the author
can explain it to me.

the formula you gave me kind of worked for 35....but it is returning a
value of one less then it should. when i enter other numbers...such
as 63 it returned a 4 instead of a 5,

need help with lookup formula
 

Hi Gil

No problem with the direct mail :)
if you click "Reply" you post in the forum
"Reply to author" sends a mail to the Author, without posting it.

I tried the shorter version of Ron as well, but you need to change
that too:
=MAX(FLOOR((E45-6)/12+1,1),4)

do you understand this formula? otherwise just ask.

cheers Carlo



On Jan 31, 11:55*am, Gil wrote:
On Jan 30, 9:15*pm, "Ron Coderre"
wrote:






=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{4,5,6,7,8,9,10,11})
You'd need to have the same number of elements
in the two array sections of your formula.

Perhaps like this:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{4,5,6,7,8,9,10,11,12 ,13,14,15})

But maybe this will work for you:
=MAX(FLOOR((E45-5)/12,1),4)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Gil" wrote in message

...

i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. *if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

thank you,

both of the previous posts seem to do the trick....i am still trying
to figure out this forum...i posted a reply to author, but don't see
it up on the forum.

the addition of the 0 in the lookup formula before the 53 did the
trick and so did the other formula *of =MAX(4,(((E45-5)-MOD(E45-5,12))/
12).

i don't understand what the second formula does, but i hope the author
can explain it to me.

the formula you gave me kind of worked for 35....but it is returning a
value of one less then it should. *when i enter other numbers...such
as 63 it returned a 4 instead of a 5,- Hide quoted text -

- Show quoted text -