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

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

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

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

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