"Between" function?

[fpd833]

Is there such a thing? I'm working on a spreadsheet with a scoring matrix
that looks like this:

A B C D E
Low High Score QScore EndScore
0 1.00 1 3.56 4
1.01 2.00 2
2.01 3.00 3
3.01 4.00 4
4.01 5.00 5

Col A is the low end of the range, Col B is the high end of the range. I'm
looking to take D2, compare it to the table above and return the appropriate
score from Col C into Col E. So essentially I am looking to take the value in
Col D, find out which range it falls within, and return the appropriate value
from Col C. I can't seem to find a function that will allow me to take the
value from Col D, find which range it's "between" and return the right value.
Make sense? Thanks in advance for your help.

[Bob Phillips]

How about

=VLOOKUP(D2,A1:C6,3)

--
HTH

RP
"fpd833" <[email protected]> wrote in message
news:[email protected]...
> Is there such a thing? I'm working on a spreadsheet with a scoring matrix
> that looks like this:
>
> A B C D E
> Low High Score QScore EndScore
> 0 1.00 1 3.56 4
> 1.01 2.00 2
> 2.01 3.00 3
> 3.01 4.00 4
> 4.01 5.00 5
>
> Col A is the low end of the range, Col B is the high end of the range. I'm
> looking to take D2, compare it to the table above and return the
appropriate
> score from Col C into Col E. So essentially I am looking to take the value
in
> Col D, find out which range it falls within, and return the appropriate
value
> from Col C. I can't seem to find a function that will allow me to take the
> value from Col D, find which range it's "between" and return the right
value.
> Make sense? Thanks in advance for your help.

[fpd833]

No go, this wont work unless the QScore in ColD is an exact match to the low
or high end of the range.

In the example I provided, the person received a QScore of 3.56
(ColD)....this is between 3.01 and 4.0 (ColA and ColB) so it should return
the number 4 from ColC in to ColE.

Thank you for the help Bob!

"Bob Phillips" wrote:

> How about
>
> =VLOOKUP(D2,A1:C6,3)
>
> --
> HTH
>
> RP
> "fpd833" <[email protected]> wrote in message
> news:[email protected]...
> > Is there such a thing? I'm working on a spreadsheet with a scoring matrix
> > that looks like this:
> >
> > A B C D E
> > Low High Score QScore EndScore
> > 0 1.00 1 3.56 4
> > 1.01 2.00 2
> > 2.01 3.00 3
> > 3.01 4.00 4
> > 4.01 5.00 5
> >
> > Col A is the low end of the range, Col B is the high end of the range. I'm
> > looking to take D2, compare it to the table above and return the
> appropriate
> > score from Col C into Col E. So essentially I am looking to take the value
> in
> > Col D, find out which range it falls within, and return the appropriate
> value
> > from Col C. I can't seem to find a function that will allow me to take the
> > value from Col D, find which range it's "between" and return the right
> value.
> > Make sense? Thanks in advance for your help.
>
>
>

[Harlan Grove]

"fpd833" <[email protected]> wrote...
>No go, this wont work unless the QScore in ColD is an exact match to the
>low
>or high end of the range.

Wrong. With no 4th argument, VLOOKUP matches the largest value in the first
column of its 2nd argument less than or equal to its 1st argument. Try
reading online help for functions suggested by others before you blithely
dismiss them.

>In the example I provided, the person received a QScore of 3.56
>(ColD)....this is between 3.01 and 4.0 (ColA and ColB) so it should return
>the number 4 from ColC in to ColE.

Why don't you just *TRY* Bob's formula? What's the worst that could happen
other than proving you don't know how VLOOKUP works?

[fpd833]

Harlan,

I didn’t dismiss the advice (blithely or otherwise). I actually did try
Bob’s suggestion and received an error. In the end the error was mine, as I
had not noticed Bob had left off the 4th argument. For this I apologize. I
will strive to be as infallible as you in the future.

Nowhere in this string did I proclaim to be an expert on how a VLOOKUP
works, or anything else for that matter. If I were an expert I would have no
reason to be posting in this newsgroup.

I would like to thank you for taking time out of your busy life to peruse
this newsgroup and clarify issues for the less educated. Despite your reply
being presumptuous, insulting and rude; this issue is much clearer for me
now. It is very comforting to know when a novice like me reaches out for help
that there are fine dilettantes such as yourself providing the subject matter
expertise. Very eloquent and reassuring.

Maybe in the future you could provide those you decide to grace with your
wisdom with a modicum of respect. What’s the worst that could happen? Someone
may be appreciative and thankful? Neither happened in this case. Your Excel
skills may be finely tuned, but your people skills leave a lot to be desired.
Kudos to you.

My true thanks go out to Bob Phillips. You sir, are a gentleman and a
scholar. Maybe Harlan will glean a small sliver of your tact and
understanding. Thanks!


"Harlan Grove" wrote:

> "fpd833" <[email protected]> wrote...
> >No go, this wont work unless the QScore in ColD is an exact match to the
> >low
> >or high end of the range.
>
> Wrong. With no 4th argument, VLOOKUP matches the largest value in the first
> column of its 2nd argument less than or equal to its 1st argument. Try
> reading online help for functions suggested by others before you blithely
> dismiss them.
>
> >In the example I provided, the person received a QScore of 3.56
> >(ColD)....this is between 3.01 and 4.0 (ColA and ColB) so it should return
> >the number 4 from ColC in to ColE.
>
> Why don't you just *TRY* Bob's formula? What's the worst that could happen
> other than proving you don't know how VLOOKUP works?
>
>
>

[Harlan Grove]

"fpd833" <[email protected]> wrote...
>I didn't dismiss the advice (blithely or otherwise). I actually did try
>Bob's suggestion and received an error. In the end the error was mine, as I
>had not noticed Bob had left off the 4th argument. For this I apologize. I
>will strive to be as infallible as you in the future.
....

Yes, do try. Perhaps just copying formulas from newsgroup postings and
pasting into Excel. That way your own manifestly inadequate typing, er,
skills wouldn't be overtaxed.

>Maybe in the future you could provide those you decide to grace with your
>wisdom with a modicum of respect. What's the worst that could happen?
....

When they DESERVE respect, they get respect. Then there are those like you.

Reread your response:

>>"fpd833" <[email protected]> wrote...
>>>No go, this wont work unless the QScore in ColD is an exact match to the
>>>low or high end of the range.
....

Don't presume you know the answer. Here's evidence you don't.