Re: lookup tables

Thanks,

that seems to work well.

how come i need to edit the array with CSE?

Phil


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



.

Relevant Pages

  • Re: lookup tables
    ... Lots of blushes - I didn't press CSE when I switched from -1 to 0!!!!! ... the ABSof the difference between the test and the array ... Bernard V Liengme ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... Try the array entered formula ... To enter or edit and array formula, use Ctrl+Shift+Enter (CSE) instead ... Bernard Liengme wrote: ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: count function
    ... you must use CSE when ... I want a running total of bat, ball, glove, using those values as a search ... All formulas array entered. ... for Cell D1 I want the function to look at column A for Ball give me ...
    (microsoft.public.excel.worksheet.functions)
  • Re: lookup tables
    ... Roger Govier ... array is descending at first and then begins to rise. ... Bernard V Liengme ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: RegExp array problem
    ... > tried to add the RegExp into there hasn't worked as yet. ... var filteredArray = new Array; ... That is, however, not the closest matches, it is returning the items with ...
    (comp.lang.javascript)
Loading