Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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] |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Closest Match | Excel Worksheet Functions | |||
need to select closest match using vlookup if it higher or lower | Excel Discussion (Misc queries) | |||
find closest match to a reference number in a row of numbers | Excel Discussion (Misc queries) | |||
Match Closest Results from Data Array | Excel Discussion (Misc queries) | |||
Closest number match help ... | Excel Worksheet Functions |