Re: lookup tables

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 ... Phil Newman wrote: ... Bernard Liengme wrote: ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... "Is there a way of searching up the list, or both up and down, ... in order to find the true closest value?" ... Bernard Liengme wrote: ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... returns the closest integer from your table (assumed to be H1:H5 as ... integers - maybe another cell with an IF in it ... ... Phil Newman wrote: ... Bernard Liengme wrote: ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... here's one which returns the minimum difference between the two closest ... Phil Newman wrote: ... Bernard Liengme wrote: ...
    (microsoft.public.excel)
  • lookup tables
    ... the closest value is 18. ... Is there a way of searching up the list, or both up and down, in order ... to find the true closest value? ... Phil Newman ...
    (microsoft.public.excel)
Loading