how do i look up the closest number valuein a lookup?
 

I'm trying to match the lookup value with the number closest (larger or
smaller) than the value. Can this be done?

how do i look up the closest number valuein a lookup?
 

use a helper column with interpolted values between your main value set.
look up on this column.

"lepche" wrote:

I'm trying to match the lookup value with the number closest (larger or
smaller) than the value. Can this be done?

how do i look up the closest number valuein a lookup?
 

Vlookup or Hlookup defaults to the number before your search value. Not sure
about finding the number above yoru search value.

"lepche" wrote:

I'm trying to match the lookup value with the number closest (larger or
smaller) than the value. Can this be done?

how do i look up the closest number valuein a lookup?
 

This can be done using an *array* formula.

Say the list of numbers is in A1 to A20.
Enter the number to lookup in C1.

Then, try this:

=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-C1)),ABS(A1:A20-C1),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"lepche" wrote in message
...
I'm trying to match the lookup value with the number closest (larger or
smaller) than the value. Can this be done?