Re: lookup tables

This is very clever.
When B1 is 17, the inner part becomes =MATCH(1,{7;5;1;5;16},-1)
But the array is not in descending order as specified by Help.
I suppose this does not matter since the match is found before the number
start to increase?
Any comment?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" <roger@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23uShAUk5GHA.3560@xxxxxxxxxxxxxxxxxxxxxxx
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
    ... Roger Govier ... array is descending at first and then begins to rise. ... Bernard V Liengme ... the closest value is 18. ...
    (microsoft.public.excel)
  • 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
    ... "Roger Govier" wrote in message ... the ABSof the difference between the test and the array is descending at first and then begins to rise. ... Bernard V Liengme ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... the ABS() of the difference between the test and the array is ... Bernard V Liengme ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: Sorting combobox elements
    ... I have a page on my web site regarding sorting arrays. ... function named QSortInPlace that will sort an array in either ... you can put the elements in the combobox into ... descending order, and then reload the array back into the combobox. ...
    (microsoft.public.excel.programming)