| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find data in table with to set of constant values
Hi all!
Please help me with following problem If I have as an example following table: 100 200 300 400 0,01 4 6 11 17 0,5 3 5 10 15 1 2 4 9 13 and I use following constant values: 0,393 to be looked up in A row as nearest value (0,5 in A3 cell) 199 to be looked up 1 coloumn as nearest value (200 in C3 cell) to look up for values in table area B2:E4 and get value 5 return. If I use this function: =INDEX(A1:E4;MATCH(G1;A1:A4;0);MATCH(G2;A1:E1;0)) it would not look up for the nearest value, regardless of rounding up the constants. I have also tried following: =INDEX(A1:E4;LOOKUP(G1;A1:A4);LOOKUP(G2;A1:E1)) but look up function only rounds down, and used constant value 0,393 become 0.01 instead of 0,5. Question: How do I get Excel to look up for the mathematicaly nearest constants in a table, and than pick a data from it? -- Best Regards Engineer |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find data in table with to set of constant values
Try this:
400 300 200 100 1 17 11 6 4 0.5 15 10 5 3 0.01 13 9 4 2 =INDEX(A1:E4,MATCH(G1,A1:A4,-1),MATCH(G2,A1:E1,-1)) HTH "Engineer" wrote: Hi all! Please help me with following problem If I have as an example following table: 100 200 300 400 0,01 4 6 11 17 0,5 3 5 10 15 1 2 4 9 13 and I use following constant values: 0,393 to be looked up in A row as nearest value (0,5 in A3 cell) 199 to be looked up 1 coloumn as nearest value (200 in C3 cell) to look up for values in table area B2:E4 and get value 5 return. If I use this function: =INDEX(A1:E4;MATCH(G1;A1:A4;0);MATCH(G2;A1:E1;0)) it would not look up for the nearest value, regardless of rounding up the constants. I have also tried following: =INDEX(A1:E4;LOOKUP(G1;A1:A4);LOOKUP(G2;A1:E1)) but look up function only rounds down, and used constant value 0,393 become 0.01 instead of 0,5. Question: How do I get Excel to look up for the mathematicaly nearest constants in a table, and than pick a data from it? -- Best Regards Engineer |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find data in table with to set of constant values
Hi Toppers!
Thank you for your answer Sorry but this does not solve my problem, becouse you cnange only "direction" of rounding up. If I than use constant greater than 0,5 I have the same problem, just in another direction. Maybe there is a method to do this without INDEX function? Creativity is reqiured. -- Best Regards Engineer "Toppers" skrev: Try this: 400 300 200 100 1 17 11 6 4 0.5 15 10 5 3 0.01 13 9 4 2 =INDEX(A1:E4,MATCH(G1,A1:A4,-1),MATCH(G2,A1:E1,-1)) HTH "Engineer" wrote: Hi all! Please help me with following problem If I have as an example following table: 100 200 300 400 0,01 4 6 11 17 0,5 3 5 10 15 1 2 4 9 13 and I use following constant values: 0,393 to be looked up in A row as nearest value (0,5 in A3 cell) 199 to be looked up 1 coloumn as nearest value (200 in C3 cell) to look up for values in table area B2:E4 and get value 5 return. If I use this function: =INDEX(A1:E4;MATCH(G1;A1:A4;0);MATCH(G2;A1:E1;0)) it would not look up for the nearest value, regardless of rounding up the constants. I have also tried following: =INDEX(A1:E4;LOOKUP(G1;A1:A4);LOOKUP(G2;A1:E1)) but look up function only rounds down, and used constant value 0,393 become 0.01 instead of 0,5. Question: How do I get Excel to look up for the mathematicaly nearest constants in a table, and than pick a data from it? -- Best Regards Engineer |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find data in table with to set of constant values
Try:
=INDEX(A1:E4,MATCH(FLOOR(G1,0.25),A1:A4,-1),MATCH(FLOOR(G2,50),A1:E1,-1)) "Engineer" wrote: Hi Toppers! Thank you for your answer Sorry but this does not solve my problem, becouse you cnange only "direction" of rounding up. If I than use constant greater than 0,5 I have the same problem, just in another direction. Maybe there is a method to do this without INDEX function? Creativity is reqiured. -- Best Regards Engineer "Toppers" skrev: Try this: 400 300 200 100 1 17 11 6 4 0.5 15 10 5 3 0.01 13 9 4 2 =INDEX(A1:E4,MATCH(G1,A1:A4,-1),MATCH(G2,A1:E1,-1)) HTH "Engineer" wrote: Hi all! Please help me with following problem If I have as an example following table: 100 200 300 400 0,01 4 6 11 17 0,5 3 5 10 15 1 2 4 9 13 and I use following constant values: 0,393 to be looked up in A row as nearest value (0,5 in A3 cell) 199 to be looked up 1 coloumn as nearest value (200 in C3 cell) to look up for values in table area B2:E4 and get value 5 return. If I use this function: =INDEX(A1:E4;MATCH(G1;A1:A4;0);MATCH(G2;A1:E1;0)) it would not look up for the nearest value, regardless of rounding up the constants. I have also tried following: =INDEX(A1:E4;LOOKUP(G1;A1:A4);LOOKUP(G2;A1:E1)) but look up function only rounds down, and used constant value 0,393 become 0.01 instead of 0,5. Question: How do I get Excel to look up for the mathematicaly nearest constants in a table, and than pick a data from it? -- Best Regards Engineer |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find data in table with to set of constant values
Hi Toppers
Thank you for your answer You know what - it works now. Thank you very much, you have been of great help. -- Best Regards Engineer "Toppers" skrev: Try: =INDEX(A1:E4,MATCH(FLOOR(G1,0.25),A1:A4,-1),MATCH(FLOOR(G2,50),A1:E1,-1)) "Engineer" wrote: Hi Toppers! Thank you for your answer Sorry but this does not solve my problem, becouse you cnange only "direction" of rounding up. If I than use constant greater than 0,5 I have the same problem, just in another direction. Maybe there is a method to do this without INDEX function? Creativity is reqiured. -- Best Regards Engineer "Toppers" skrev: Try this: 400 300 200 100 1 17 11 6 4 0.5 15 10 5 3 0.01 13 9 4 2 =INDEX(A1:E4,MATCH(G1,A1:A4,-1),MATCH(G2,A1:E1,-1)) HTH "Engineer" wrote: Hi all! Please help me with following problem If I have as an example following table: 100 200 300 400 0,01 4 6 11 17 0,5 3 5 10 15 1 2 4 9 13 and I use following constant values: 0,393 to be looked up in A row as nearest value (0,5 in A3 cell) 199 to be looked up 1 coloumn as nearest value (200 in C3 cell) to look up for values in table area B2:E4 and get value 5 return. If I use this function: =INDEX(A1:E4;MATCH(G1;A1:A4;0);MATCH(G2;A1:E1;0)) it would not look up for the nearest value, regardless of rounding up the constants. I have also tried following: =INDEX(A1:E4;LOOKUP(G1;A1:A4);LOOKUP(G2;A1:E1)) but look up function only rounds down, and used constant value 0,393 become 0.01 instead of 0,5. Question: How do I get Excel to look up for the mathematicaly nearest constants in a table, and than pick a data from it? -- Best Regards Engineer |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I restore original Pivot Table data? | Excel Discussion (Misc queries) | |||
| How to Append the Data to the Master Table | Excel Worksheet Functions | |||
| Change Data In Pivot Table | New Users to Excel | |||
| HOW DO I FIND DATA IN A TABLE BY LOOKING UP BOTH THE COLUMN AND R. | Excel Worksheet Functions | |||
| Data Table - does it work with DDE links and Stock Tickers? | Excel Worksheet Functions | |||
Linear Mode
