"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)

 

[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!

 

[Harlan Grove]

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 did not dismiss Bob's advice and I actually did try it. When I did, I
didn't notice he'd left off the 4th argument and I received an error...hence
my reply. It was my error for not reading it carefully enough, for that I
apologize.

Nowhere in this string did I contend to know how the VLOOKUP function
works...if I were an expert such as yourself I would have no need to be
visiting this newsgroup. I would like to thank you for pointing out my error
and for providing an explanation. I would also like to thank you for
presenting your answer in a way only a true asshole can. Those of us looking
for help are certainly searching for someone like you to provide the answer
JUST the way you did.

Why couldn't you provide your answer with a modicum of respect? What's the
worst that could happen? Someone may be appreciative? Someone may respect
you? Neither happened in this case. Kudos to you.

My true thanks goes to Bob Phillips. You are a true gentleman and a scholar.

 

[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]

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:
....

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