View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg Herbert Seidenberg is offline
external usenet poster
 
Posts: 1,180
Default Lookup Closest Value

Assuming there are no duplicates in your array,
enter as an array formula
=SUMPRODUCT(((ABS(array-target))=MIN(ABS(array-target)))*array)
otherwise use this array formula for the first closest match in a list
=INDEX(list,MATCH(MIN(ABS(list-target)),(ABS(list-target)),0))