Re: lookup tables

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
    ... 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
    ... 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: Sumproduct puzzler - apologies for incorrect post to Programmi
    ... One way would be the array entered formula ... To enter or amend an array formula, use Control+Shift+Enter (CSE) not just Enter. ... On Dec 15, 10:32 am, Bony Pony ...
    (microsoft.public.excel.misc)
  • Re: A validation rule on Alpha and Numeric characters
    ... Use Data> Validation>Custom> and array entered formula of ... When you use CSE, Excel ... On the Error Alert tab type a message for the use showing the type of ... reference that i can prevent changing its format. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Sum x number of right cells after finding first cell more than 0.
    ... One way would be with the array entered formula ... To enter, or amend, an array formula use Control+Shift+Enter (CSE) not just Enter ... I am finding a solution to automatically sum cells ...
    (microsoft.public.excel.worksheet.functions)