Re: lookup tables

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I would think that using 0 (for an exact match) instead of -1 would be safer.

Roger Govier wrote:

Hi Phil

Try the array entered formula
{=INDEX(A1:A5,MATCH(MIN(ABS($B$1-A1:A5)),ABS(($B$1-A1:A5)),-1))}

To enter or edit and array formula, use Ctrl+Shift+Enter (CSE) instead
of Enter.
Do not type the curly braces { } yourself, Excel will enter then when
you use CSE.
--
Regards

Roger Govier

"Phil Newman" <phillenium2002@xxxxxxxxxxx> wrote in message
news:1159806836.864499.162590@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
actually, that doesn't work too great, because it now only chooses to
go up, rather than decide which is closer...

for example, i have 33.8, and the values are 33 and 39. it now
chooses
39 as the closest!

Phil


Phil Newman wrote:
blimey, that IS long-winded! but it works great, so thanks very much!

Phil


Bernard Liengme wrote:
A bit long winded but
=IF(ISNA(VLOOKUP(A3,$E$1:$E$5,1,0)),INDEX($E$1:$E$5,MATCH(A3,$E$1:$E$5,1)+1),VLOOKUP(A3,$E$1:$E$5,1,0))
The value to lookup (15.086) is in A1 and the table of values in
E1:E5
The INDEX/MATCH does the advancing by 1 but we need to avoid this
if an
exact match is found.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Phil Newman" <phillenium2002@xxxxxxxxxxx> wrote in message
news:1159802807.222940.63210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
ok,

i have a value, 15.086, which i want to look up in a table of
values
which are all integers, shown below.

10
12
18
22
33


Mathematically, the closest value is 18. However, when i use
VLOOKUP
it tells me that 12 is the closest, because it searches down the
list,
and not up.

Is there a way of searching up the list, or both up and down, in
order
to find the true closest value?

Regards,

Phil Newman



--

Dave Peterson
.

Relevant Pages

  • Re: lookup tables
    ... I don't think we could use 0, as it is unlikely that an exact match will ... Roger Govier wrote: ... To enter or edit and array formula, ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... Roger Govier ... Phil Newman wrote: ... Bernard Liengme wrote: ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... Of course, unsorted, if the larger value is displayed first, it will be the returned value. ... "Roger Govier" wrote in message ... the ABSof the difference between the test and the array is descending at first and then begins to rise. ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... the *higher* value when the lookup value is *exactly between* the integers in the datalist. ... "Roger Govier" wrote in message ... the ABSof the difference between the test and the array is descending at first and then begins to rise. ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... Isn't that tenacity? ... Dave Peterson wrote: ... Roger Govier wrote: ... closest to the given value.) ...
    (microsoft.public.excel)