| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Closest value lookup?
I have a sheet with specific numbers. On another sheet, the user would input
a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest value lookup?
Is your list of numbers sorted? In what order, ascending or descending? You
want the closest whether it's over or under? Biff "ADK" wrote in message ... I have a sheet with specific numbers. On another sheet, the user would input a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest value lookup?
over, under...doesn't matter....which ever is closer
Example of chart below: 6 6 1/4 6 1/2 6 5/8 6 11/16 6 3/4 6 13/16 1 6 6.25 6.5 6.625 6.6875 6.75 6.8125 2 100 100.5 101 101.25 101.375 101.5 101.625 3 106 106.75 107.5 107.875 108.0625 108.25 108.4375 4 200 201 202 202.5 202.75 203 203.25 5 206 207.25 208.5 209.125 209.4375 209.75 210.0625 6 300 301.5 303 303.75 304.125 304.5 304.875 7 306 307.75 309.5 310.375 310.8125 311.25 311.6875 8 400 402 404 405 405.5 406 406.5 9 406 408.25 410.5 411.625 500.1875 500.75 501.3125 10 500 502.5 505 506.25 506.875 507.5 508.125 11 506 508.75 511.5 600.875 601.5625 602.25 602.9375 12 600 603 606 607.5 608.25 609 609.75 13 606 609.25 700.5 702.125 702.9375 703.75 704.5625 14 700 703.5 707 708.75 709.625 710.5 711.375 15 706 709.75 801.5 803.375 804.3125 805.25 806.1875 16 800 804 808 810 811 900 901 17 806 810.25 902.5 904.625 905.6875 906.75 907.8125 18 900 904.5 909 911.25 1000.375 1001.5 1002.625 19 906 910.75 1003.5 1005.875 1007.0625 1008.25 1009.4375 20 1000 1005 1010 1100.5 1101.75 1103 1104.25 21 1006 1011.25 1104.5 1107.125 1108.4375 1109.75 1111.0625 22 1100 1105.5 1111 1201.75 1203.125 1204.5 1205.875 23 1106 1111.75 1205.5 1208.375 1209.8125 1211.25 1300.6875 "T. Valko" wrote in message ... Is your list of numbers sorted? In what order, ascending or descending? You want the closest whether it's over or under? Biff "ADK" wrote in message ... I have a sheet with specific numbers. On another sheet, the user would input a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest value lookup?
sorry, looked better before sending....hopefully this image is ok
"ADK" wrote in message ... over, under...doesn't matter....which ever is closer Example of chart below: 6 6 1/4 6 1/2 6 5/8 6 11/16 6 3/4 6 13/16 1 6 6.25 6.5 6.625 6.6875 6.75 6.8125 2 100 100.5 101 101.25 101.375 101.5 101.625 3 106 106.75 107.5 107.875 108.0625 108.25 108.4375 4 200 201 202 202.5 202.75 203 203.25 5 206 207.25 208.5 209.125 209.4375 209.75 210.0625 6 300 301.5 303 303.75 304.125 304.5 304.875 7 306 307.75 309.5 310.375 310.8125 311.25 311.6875 8 400 402 404 405 405.5 406 406.5 9 406 408.25 410.5 411.625 500.1875 500.75 501.3125 10 500 502.5 505 506.25 506.875 507.5 508.125 11 506 508.75 511.5 600.875 601.5625 602.25 602.9375 12 600 603 606 607.5 608.25 609 609.75 13 606 609.25 700.5 702.125 702.9375 703.75 704.5625 14 700 703.5 707 708.75 709.625 710.5 711.375 15 706 709.75 801.5 803.375 804.3125 805.25 806.1875 16 800 804 808 810 811 900 901 17 806 810.25 902.5 904.625 905.6875 906.75 907.8125 18 900 904.5 909 911.25 1000.375 1001.5 1002.625 19 906 910.75 1003.5 1005.875 1007.0625 1008.25 1009.4375 20 1000 1005 1010 1100.5 1101.75 1103 1104.25 21 1006 1011.25 1104.5 1107.125 1108.4375 1109.75 1111.0625 22 1100 1105.5 1111 1201.75 1203.125 1204.5 1205.875 23 1106 1111.75 1205.5 1208.375 1209.8125 1211.25 1300.6875 "T. Valko" wrote in message ... Is your list of numbers sorted? In what order, ascending or descending? You want the closest whether it's over or under? Biff "ADK" wrote in message ... I have a sheet with specific numbers. On another sheet, the user would input a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest value lookup?
Ok, so do you want to find the closest number in column B and return one of
the numbers on the same row from one of the other columns? Give me an example based on the screencap you posted. Biff "ADK" wrote in message ... sorry, looked better before sending....hopefully this image is ok "ADK" wrote in message ... over, under...doesn't matter....which ever is closer Example of chart below: 6 6 1/4 6 1/2 6 5/8 6 11/16 6 3/4 6 13/16 1 6 6.25 6.5 6.625 6.6875 6.75 6.8125 2 100 100.5 101 101.25 101.375 101.5 101.625 3 106 106.75 107.5 107.875 108.0625 108.25 108.4375 4 200 201 202 202.5 202.75 203 203.25 5 206 207.25 208.5 209.125 209.4375 209.75 210.0625 6 300 301.5 303 303.75 304.125 304.5 304.875 7 306 307.75 309.5 310.375 310.8125 311.25 311.6875 8 400 402 404 405 405.5 406 406.5 9 406 408.25 410.5 411.625 500.1875 500.75 501.3125 10 500 502.5 505 506.25 506.875 507.5 508.125 11 506 508.75 511.5 600.875 601.5625 602.25 602.9375 12 600 603 606 607.5 608.25 609 609.75 13 606 609.25 700.5 702.125 702.9375 703.75 704.5625 14 700 703.5 707 708.75 709.625 710.5 711.375 15 706 709.75 801.5 803.375 804.3125 805.25 806.1875 16 800 804 808 810 811 900 901 17 806 810.25 902.5 904.625 905.6875 906.75 907.8125 18 900 904.5 909 911.25 1000.375 1001.5 1002.625 19 906 910.75 1003.5 1005.875 1007.0625 1008.25 1009.4375 20 1000 1005 1010 1100.5 1101.75 1103 1104.25 21 1006 1011.25 1104.5 1107.125 1108.4375 1109.75 1111.0625 22 1100 1105.5 1111 1201.75 1203.125 1204.5 1205.875 23 1106 1111.75 1205.5 1208.375 1209.8125 1211.25 1300.6875 "T. Valko" wrote in message ... Is your list of numbers sorted? In what order, ascending or descending? You want the closest whether it's over or under? Biff "ADK" wrote in message ... I have a sheet with specific numbers. On another sheet, the user would input a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest value lookup?
Row 1 and Column A are resultants in the chart based on user input value
Example 1: a user inputs a number in A1 (input sheet) such as: 208.75 the closest value would be 208.5 (cell D6) (data Sheet) Cell A2 (input sheet) would have the formula and return the value 5 (cell A6) Cell A3 (input sheet) would have the formula and return the value 6 1/2 (cell D1) Example 2: a user inputs a number in A1 (input sheet) such as: 810 the closest value would be 810.25 (cell C18) (data Sheet) Cell A2 (input sheet) would have the formula and return the value 17 (cell A18) Cell A3 (input sheet) would have the formula and return the value 6 1/4 (cell C1) HTH explain the closest in the screenshot would be "T. Valko" wrote in message ... Ok, so do you want to find the closest number in column B and return one of the numbers on the same row from one of the other columns? Give me an example based on the screencap you posted. Biff "ADK" wrote in message ... sorry, looked better before sending....hopefully this image is ok "ADK" wrote in message ... over, under...doesn't matter....which ever is closer Example of chart below: 6 6 1/4 6 1/2 6 5/8 6 11/16 6 3/4 6 13/16 1 6 6.25 6.5 6.625 6.6875 6.75 6.8125 2 100 100.5 101 101.25 101.375 101.5 101.625 3 106 106.75 107.5 107.875 108.0625 108.25 108.4375 4 200 201 202 202.5 202.75 203 203.25 5 206 207.25 208.5 209.125 209.4375 209.75 210.0625 6 300 301.5 303 303.75 304.125 304.5 304.875 7 306 307.75 309.5 310.375 310.8125 311.25 311.6875 8 400 402 404 405 405.5 406 406.5 9 406 408.25 410.5 411.625 500.1875 500.75 501.3125 10 500 502.5 505 506.25 506.875 507.5 508.125 11 506 508.75 511.5 600.875 601.5625 602.25 602.9375 12 600 603 606 607.5 608.25 609 609.75 13 606 609.25 700.5 702.125 702.9375 703.75 704.5625 14 700 703.5 707 708.75 709.625 710.5 711.375 15 706 709.75 801.5 803.375 804.3125 805.25 806.1875 16 800 804 808 810 811 900 901 17 806 810.25 902.5 904.625 905.6875 906.75 907.8125 18 900 904.5 909 911.25 1000.375 1001.5 1002.625 19 906 910.75 1003.5 1005.875 1007.0625 1008.25 1009.4375 20 1000 1005 1010 1100.5 1101.75 1103 1104.25 21 1006 1011.25 1104.5 1107.125 1108.4375 1109.75 1111.0625 22 1100 1105.5 1111 1201.75 1203.125 1204.5 1205.875 23 1106 1111.75 1205.5 1208.375 1209.8125 1211.25 1300.6875 "T. Valko" wrote in message ... Is your list of numbers sorted? In what order, ascending or descending? You want the closest whether it's over or under? Biff "ADK" wrote in message ... I have a sheet with specific numbers. On another sheet, the user would input a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Closest value lookup?
You might try the "closest match lookups" section on my Lookups page.
http://www.cpearson.com/excel/lookups.htm about 2/3 of the way down. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "ADK" wrote in message ... I have a sheet with specific numbers. On another sheet, the user would input a value. I like excel to find that number or the closest number then return the value in row/column X. Example: User inputs 124.5 The closest value in lookup is 124.25 then formula in the next cell returns the value in row/column X Any way of doing this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i look up the closest number valuein a lookup? | Excel Discussion (Misc queries) | |||
| Lookup Closest Value | Excel Worksheet Functions | |||
| closest match | Excel Worksheet Functions | |||
| vlookup-Closest value | New Users to Excel | |||
| Lookup closest number in list | Excel Discussion (Misc queries) | |||
Hybrid Mode
