Re: lookup tables

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: How to VLOOKUP in XCEL across different workbooks each named d
    ... "Bernard Liengme" wrote: ... This does the lookup and returns the value 3 ... Microsoft Excel MVP ... I don't know how to incorporate the different source filename in ...
    (microsoft.public.excel.worksheet.functions)
  • Re: vlookup -- converting numbers to text
    ... "Bernard Liengme" wrote: ... I will change named range to "Items" instead of Lookup. ...
    (microsoft.public.excel)
  • Re: lookup tables
    ... Roger Govier ... Phil Newman wrote: ... Bernard Liengme wrote: ... the closest value is 18. ...
    (microsoft.public.excel)
  • Re: How to VLOOKUP in XCEL across different workbooks each named d
    ... Since "book two" has a different name for each LOOKUP, ... "Bernard Liengme" wrote: ... Edit this to remove reference to the second workbook for the first argument ... I don't know how to incorporate the different source filename in my ...
    (microsoft.public.excel.worksheet.functions)
  • 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)