Hi,
Via the INDEX and MATCH functions I can get the intersection of a column and
raw cells in the multiplication table. Is there a reverse formula that shows
a value from the first column, using two values, one from corresponding
intersection and the other from the first row of the multiplication table.

To ilustrate:

1 2 3 4
2 4 6 8
3 6 9 12
4 8 12 16

by typing 2 (from the first row) and 3 (from the first column), the INDEX
and MATCH functions give 6, an intersection of 2 and 3 from the table.
A desired formula should give me 3(from the first column), by typing 2 (from
the first row) and 6 (intersection).

Your help is immensely appreciated.

karl

One way

Source table assumed in A1:D4

Inputs
in F1: 2 (value in 1st col),
in G1: 6 (value in 2nd row)

Then in H1:
=INDEX($A$1:$D$1,MATCH(G1,OFFSET($A$1:$D$1,MATCH(F 1,$A$1:$A$4,0)-1,),0))
will return the corresponding value in the 1st row (within A1:D1), ie: 3.
Copy H1 down to return as required for other input pairs in F2:G2, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"karl" wrote:
Hi,
Via the INDEX and MATCH functions I can get the intersection of a column and
raw cells in the multiplication table. Is there a reverse formula that shows
a value from the first column, using two values, one from corresponding
intersection and the other from the first row of the multiplication table.

To ilustrate:

1 2 3 4
2 4 6 8
3 6 9 12
4 8 12 16

by typing 2 (from the first row) and 3 (from the first column), the INDEX
and MATCH functions give 6, an intersection of 2 and 3 from the table.
A desired formula should give me 3(from the first column), by typing 2 (from
the first row) and 6 (intersection).

Your help is immensely appreciated.

karl

If your sample table is *real* then all you need to do is divide:

give me 3...by typing 2...and 6

=6/2


--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
Hi,
Via the INDEX and MATCH functions I can get the intersection of a column
and
raw cells in the multiplication table. Is there a reverse formula that
shows
a value from the first column, using two values, one from corresponding
intersection and the other from the first row of the multiplication table.

To ilustrate:

1 2 3 4
2 4 6 8
3 6 9 12
4 8 12 16

by typing 2 (from the first row) and 3 (from the first column), the INDEX
and MATCH functions give 6, an intersection of 2 and 3 from the table.
A desired formula should give me 3(from the first column), by typing 2
(from
the first row) and 6 (intersection).

Your help is immensely appreciated.

karl

Hi Max,

thanks for the formula, it did work on that specific selection (A1:D4). for
some reason though, when I try to expand the selection to include all my data
( A100:X100), the formula does not seem to work. any idea?
thanks again.
karl

"Max" wrote:

One way

Source table assumed in A1:D4

Inputs
in F1: 2 (value in 1st col),
in G1: 6 (value in 2nd row)

Then in H1:
=INDEX($A$1:$D$1,MATCH(G1,OFFSET($A$1:$D$1,MATCH(F 1,$A$1:$A$4,0)-1,),0))
will return the corresponding value in the 1st row (within A1:D1), ie: 3.
Copy H1 down to return as required for other input pairs in F2:G2, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"karl" wrote:
Hi,
Via the INDEX and MATCH functions I can get the intersection of a column and
raw cells in the multiplication table. Is there a reverse formula that shows
a value from the first column, using two values, one from corresponding
intersection and the other from the first row of the multiplication table.

To ilustrate:

1 2 3 4
2 4 6 8
3 6 9 12
4 8 12 16

by typing 2 (from the first row) and 3 (from the first column), the INDEX
and MATCH functions give 6, an intersection of 2 and 3 from the table.
A desired formula should give me 3(from the first column), by typing 2 (from
the first row) and 6 (intersection).

Your help is immensely appreciated.

karl

... expand the selection to include all my data (A100:X100)

Your adapted formula for the above should look something like this:
=INDEX($A$100:$X$100,MATCH(G1,OFFSET($A$100:$X$100 ,MATCH(F1,$A$100:$A$400,0)-1,),0))

Don't forget to adapt the 1st col's match range: $A$1:$A$4
in the earlier expression to say: $A$100:$A$400

(Always post your attempted formula)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"karl" wrote in message
...
Hi Max,

thanks for the formula, it did work on that specific selection (A1:D4).
for
some reason though, when I try to expand the selection to include all my
data
( A100:X100), the formula does not seem to work. any idea?
thanks again.
karl

"Max" wrote...
....
Your adapted formula for the above should look something like this:

=INDEX($A$100:$X$100,MATCH(G1,OFFSET($A$100:$X$10 0,
MATCH(F1,$A$100:$A$400,0)-1,),0))
....

This finds the value in the top row given values in the leftmost
column and the interior of the table. OP originally asked for the
value in the leftmost column given values in the topmost row and the
interior of the table. ['A desired formula should give me 3(from the
first column), by typing 2 (from the first row) and 6
(intersection).'] The OP's overly simple original example range is
symmetric, so your formula would appear to give correct results even
though it does something different than the OP requested.

No need to use the volatile OFFSET function. Use a second INDEX call.

If the full table were named Tbl, the value sought in the top row of
Tbl named ColVal, and the value sought in the lower-right portion of
Tbl named TblVal, the corresponding value in the leftmost column would
be given by

=INDEX(Tbl,MATCH(TblVal,INDEX(Tbl,0,MATCH(ColVal,I NDEX(Tbl,1,0)))),1)

Or you could simplify a formula using literal range addresses.

=INDEX($A$100:$A$123,MATCH(G1,INDEX($A$100:$X$123,
MATCH(F1,$A$100:$X$100),0)))

All these formulas assume the top row and leftmost column values are
strictly increasing. If the values are distinct but unordered, then
the OP would need to include 3rd arguments of zero in the MATCH calls.
If there could be duplicate values in the top row or leftmost column,
as long as there were distinct values in the interior of the table,
the problem would still be well-defined, but the necessary formula
would be much, much more complicated.

Karl, my sincere apologies,

As Harlan stated correctly in his posting, I had mis-read your original
posting and suggested the wrong formula.

The correct 1st formula for your original posting should have been:
=INDEX($A$1:$A$4,MATCH(G1,OFFSET($A$1:$A$4,,MATCH( F1,$A$1:$D$1,0)-1,),0))

where inputs
in G1: 6 (internal value within B2:D4)
in F1: 2 (value in 1st row vertically above the internal value in G1)

And the corrected adaptation for your "actual situation"
should read something like this:
=INDEX($A$100:$A$400,MATCH(G1,OFFSET($A$100:$A$400 ,,MATCH(F1,$A$100:$X$100,0)-1,),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Harlan Grove" wrote
... This finds the value in the top row given values in the leftmost
column and the interior of the table. OP originally asked for the
value in the leftmost column given values in the topmost row and the
interior of the table. ['A desired formula should give me 3(from the
first column), by typing 2 (from the first row) and 6
(intersection).'] The OP's overly simple original example range is
symmetric, so your formula would appear to give correct results even
though it does something different than the OP requested...

Agreed fully with the above. My mistake in mis-reading the original post
wrongly earlier. Thanks for pointing that out.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you Max and Harlan!!!!

"Max" wrote:

Karl, my sincere apologies,

As Harlan stated correctly in his posting, I had mis-read your original
posting and suggested the wrong formula.

The correct 1st formula for your original posting should have been:
=INDEX($A$1:$A$4,MATCH(G1,OFFSET($A$1:$A$4,,MATCH( F1,$A$1:$D$1,0)-1,),0))

where inputs
in G1: 6 (internal value within B2:D4)
in F1: 2 (value in 1st row vertically above the internal value in G1)

And the corrected adaptation for your "actual situation"
should read something like this:
=INDEX($A$100:$A$400,MATCH(G1,OFFSET($A$100:$A$400 ,,MATCH(F1,$A$100:$X$100,0)-1,),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

welcome, Karl
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"karl" wrote in message
...
Thank you Max and Harlan!!!!