Find data in table with to set of constant values

[Guest]

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?

 

[Guest]

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

 

[Guest]

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:

 

[Guest]

Try:


=INDEX(A1:E4,MATCH(FLOOR(G1,0.25),A1:A4,-1),MATCH(FLOOR(G2,50),A1:E1,-1))

 

[Guest]

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: