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

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

Guest

I'm trying to match the lookup value with the number closest (larger or
smaller) than the value. Can this be done?
 
use a helper column with interpolted values between your main value set.
look up on this column.
 
Vlookup or Hlookup defaults to the number before your search value. Not sure
about finding the number above yoru search value.
 
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!
=====================================================


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find closest match to a reference number in a row of numbers 4
Sort Absolute Values 2
How to determine the closed value? 7
I need help with LOOKUP 4
formatting a lookup 4
Match 9 to 11 digits - vlookup 3
How to determine the closest value? 7
How to determine the closest value? 2

Back
Top