closest match

[sandip.dhamapurkar]

I have a list of numbers starting from 50000 to 5000000 in the ragne
"A1:A100")

If I type a value in cell b1, cell c1 should show the nearest value in
the list given in column a

eg. If I type 142000 in cell b1, c1 should show 150000 as it is the
nearest
If 174000 in b1 then 150000 in c1
if 184000 in b1 then 200000 in c1

Thanks

 

[macropod]

Hi sandip,

Try:
=IF(B1-INDEX(A$1:A$100,MATCH(B1,A$1:A$100,1))<OFFSET(INDEX(A$1:A$100,MATCH(B
1,A$1:A$100,1)),1,0)-B1,INDEX(A$1:A$100,MATCH(B1,A$1:A$100,1)),OFFSET(INDEX(
A$1:A$100,MATCH(B1,A$1:A$100,1)),1,0))

Cheers

 

[sandip.dhamapurkar]

Thank you

 

[Domenic]

Try...

=INDEX(A1:A100,MATCH(MIN(ABS(A1:A100-B1)),ABS(A1:A100-B1),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[sandip.dhamapurkar]

Thank you.