vlookup-Closest value

Dear Friends,

How can I use Vlookup to give me the closest value greather than or equal to
vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2
It chooses 5.1 and give me the corresponding value.

Thank you,

atatari wrote:
> Dear Friends,
>
> How can I use Vlookup to give me the closest value greather than or equal to
> vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2
> It chooses 5.1 and give me the corresponding value.
>
> Thank you,

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The default behaviour of Vlookup will;
basically assign everything between (and including) 4.9 and less than
5.1 to the 4.9 value. So 4.9 <= x < 5.1
So the value 5 will actually get hooked up with the 4.9 value which does
not appear to be what you want.

Try using MATCH and INDEX, this is just one work around.
Sort your values in reverse order eg.
Col A, Col B
5.2 AnswerFor5.2
5.1 AnswerFor5.1
4.9 AnswerFor4.9

So the answer you are after is =INDEX(B1:B3,MATCH(5,A1:A3,-1))
This will return "AnswerFor5.1"

The -1 tells us to look in descending instead of ascending order.
And you can change the number 5 to point to a cell you wish to look up.
With this method Columns A and B don't even have to be next to each
other. B1:B3 could quite easily have been column Z eg. 'Z1:Z3' or even
in the same column A eg. 'A11:A13'

Hope this helps
George

Not possible using vlookup, in an unsorted list you can use

=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"<"&200)+1),A1:A10,0))

will lookup a value that is greater or equal to 200 in A and return the
value from B
so if it would work in a vlookup it might have looked like

=VLOOKUP(200,A1:B10,2 and so on

--
Regards,

Peo Sjoblom

Portland, Oregon




"atatari" <[email protected]> wrote in message
news:[email protected]...
> Dear Friends,
>
> How can I use Vlookup to give me the closest value greather than or equal
> to
> vlookup value.For example if my lookup value is 5 and I have 4.9 and
> 5.1,5.2
> It chooses 5.1 and give me the corresponding value.
>
> Thank you,

By default, if the first column of your lookup range is numeric and
sorted and the fourth parameter in your lookup is set to "true", Excel
will search for an exact match and return the requested value. If it
can't find an exact match it will give you the requested value (column)
of the highest value in column one that is less than the search value.

Here's an example:

=lookup(A1;F1:G5;2;true)

lookup search value
key from/to
10 00 - 9.999...
20 10 - 29.999...
30 20 - .....

So in order to achieve you result and use vlookup you would need to
redesign your lookup table to put the expected value in the proper
place

Your table (yours reads: anything greater than 4.9 and less than 5.1 =
75.12; but e.g 4.0 would result in #NA)

4.9 123.11
5.1 75.12
5.2 112.80

redesigned (this reads: anything from 0 upto but not including 4.9 =
123.11; anything from 4.9 upto but not including 5.1 = 75.12 etc)

0.0 123.11
4.9 75.12
5.1 112.80
5.2 ???
...

Hope this helps.

Hans

"unsorted"

--
Regards,

Peo Sjoblom

Portland, Oregon




"flummi" <[email protected]> wrote in message
news:[email protected]...
> By default, if the first column of your lookup range is numeric and
> sorted and the fourth parameter in your lookup is set to "true", Excel
> will search for an exact match and return the requested value. If it
> can't find an exact match it will give you the requested value (column)
> of the highest value in column one that is less than the search value.
>
> Here's an example:
>
> =lookup(A1;F1:G5;2;true)
>
> lookup search value
> key from/to
> 10 00 - 9.999...
> 20 10 - 29.999...
> 30 20 - .....
>
> So in order to achieve you result and use vlookup you would need to
> redesign your lookup table to put the expected value in the proper
> place
>
> Your table (yours reads: anything greater than 4.9 and less than 5.1 =
> 75.12; but e.g 4.0 would result in #NA)
>
> 4.9 123.11
> 5.1 75.12
> 5.2 112.80
>
> redesigned (this reads: anything from 0 upto but not including 4.9 =
> 123.11; anything from 4.9 upto but not including 5.1 = 75.12 etc)
>
> 0.0 123.11
> 4.9 75.12
> 5.1 112.80
> 5.2 ???
> ..
>
> Hope this helps.
>
> Hans
>

Hi Peon,

I said "By default, IF the first column is sorted", not "by default the
first column IS sorted".

:-)

Greatings to Portland!

Hans