Lookup Closest Value

[Guest]

Is there a way that I can lookup the closest value of a given number in a
table of data using Index/Match?

I am looking for a formula that will provide the same functionality as
setting the VLOOKUP Range value to true but allows me to have more flexibilty
as to the data I can work with. Any help with this would be greatly
appreciated.

Chad

 

[Guest]

HI Chad

=vlookup(a1,b2:c100,2,1)

adjust your range
the 1 at the end will return the close figure
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Chad" escreveu:

 

[Domenic]

Can you provide a sample of the data, along with actual/expected results?

 

[Herbert Seidenberg]

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))