Pulling hair out with VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I used VLOOKUP in a spreadsheet some time ago, where I had the column set to
assign a rating based on results. When I used it previously, it returned
results based on the number in the column being the HIGH end of the range for
each rating.

Example
10 - Rating 1
25 - Rating 2
50 - Rating 3 and so on.

So, anything that fell between 26- 50 would be in Rating 3.

This time it is using the number in each rating as the START of the range.

I used a QUARTILES function to come up with quartiles for a group of
numbers, and it appears to pick the high end number for each quartile. I
tried using the VLOOKUP to assign the correct Quartile to the lookup_value,
but it is not working out as it should.

Does anyone have ideas for what I am doing wrong, or for a better way to
assign quartiles to a group of numbers?

Thanks!
 
I was using the following formula:

=VLOOKUP(J19, $K$9:$L$14,2, TRUE)

This is the same formula I used before and it would choose the number as a
continuous range (but high end of the range).
 
Hi
the list has to contain the LOWER end of the range and has to be sorted
ascending
 
OK, quick question. The Quartiles function gives numbers that are at the
highest end of the range. Is there a way to change that to give lowest end of
the range?
 
Back
Top