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
|