closest match
 

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

closest match
 

Hi sandip,

Try:
=IF(B1-INDEX(A$1:A$100,MATCH(B1,A$1:A$100,1))<OFFSET(INDE X(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(I NDEX(
A$1:A$100,MATCH(B1,A$1:A$100,1)),1,0))

Cheers
--
macropod
[MVP - Microsoft Word]


wrote in message
ups.com...
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

closest match
 

Thank you

macropod wrote:
Hi sandip,

Try:
=IF(B1-INDEX(A$1:A$100,MATCH(B1,A$1:A$100,1))<OFFSET(INDE X(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(I NDEX(
A$1:A$100,MATCH(B1,A$1:A$100,1)),1,0))

Cheers
--
macropod
[MVP - Microsoft Word]

closest match
 

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!

In article . com,
wrote:

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

closest match
 

Thank you.

Domenic wrote:
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!