software

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:
gt; Dear Friends,
gt;
gt; How can I use Vlookup to give me the closest value greather than or equal to
gt; vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2
gt; It chooses 5.1 and give me the corresponding value.
gt;
gt; Thank you,

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_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 lt;= x lt; 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.2AnswerFor5.2
5.1AnswerFor5.1
4.9AnswerFor4.9

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

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,quot;lt; quot;amp;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

quot;atatariquot; gt; wrote in message
...
gt; Dear Friends,
gt;
gt; How can I use Vlookup to give me the closest value greather than or equal
gt; to
gt; vlookup value.For example if my lookup value is 5 and I have 4.9 and
gt; 5.1,5.2
gt; It chooses 5.1 and give me the corresponding value.
gt;
gt; 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 quot;truequot;, 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.

Hansquot;unsortedquot;

--
Regards,

Peo Sjoblom

Portland, Oregon

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

I said quot;By default, IF the first column is sortedquot;, not quot;by default the
first column IS sortedquot;.

:-)

Greatings to Portland!

Hans