Re: lookup tables

Hi RD

I have just re-tested my data and I agree it works both ways with 0 as
the parameter.

Lots of blushes - I didn't press CSE when I switched from -1 to 0!!!!!

--
Regards

Roger Govier


"RagDyer" <RagDyer@xxxxxxxxxxxxx> wrote in message
news:OJpTvtm5GHA.4616@xxxxxxxxxxxxxxxxxxxxxxx
This works for me with *or* without a sorted data list:

=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-B1)),ABS(A1:A5-B1),0))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Roger Govier" <roger@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:uRB9llm5GHA.4644@xxxxxxxxxxxxxxxxxxxxxxx
Hi Bernard

The array being tested is in ascending order in the OP supplied and
in my test.
Therefore, the ABS() of the difference between the test and the array
is descending at first and then begins to rise.
As you rightly say, because we are looking for the MIN, that gets
found before the numbers begin to rise again, hence the solution
seems to work with the -1 parameter.
I cannot get it to work with 0, or with 1. It sometimes works with 1,
if the value being tested is integer, but not with a decimal value.

Whilst in the true sense, it does not follow the rule for Index,
because of the Min and Abs it seems to work.

Having just written the above, I did just create another list with
numbers in random order, and again the formula works with -1 but
fails with either 0 or 1.
I am now at a loss to explain why it works, other than as far as my
testing seems to be concerned - it does!!!!

--
Regards

Roger Govier


"Bernard Liengme" <bliengme@xxxxxxxxxxxxxxxxx> wrote in message
news:uWtdNDl5GHA.1256@xxxxxxxxxxxxxxxxxxxxxxx
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
    ... how come i need to edit the array with CSE? ... Try the array entered formula ... To enter or edit and array formula, use Ctrl+Shift+Enter (CSE) instead ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... But the array is not in descending order as specified by Help. ... 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)