| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Closest Value | Excel Worksheet Functions | |||
| How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
| How can i round a number to closest tenth number? | Excel Worksheet Functions | |||
| Lookup closest number in list | Excel Discussion (Misc queries) | |||
| Closest number match help ... | Excel Worksheet Functions | |||
Linear Mode
