# I need suggestions

Posted by Marvin on August 03, 2001 6:24 AM

As an example lets say I have a multiplication table. The top most row has the labels 1-10 and the same for the left most column. To find what 8 * 3 equals (24)you simply follow the 8 column and the 3 row till they meet each other. However I would like to build a function or something within Excel that I can enter the top row value and the answer and get the left most column value that matches. Can anyone help? Thanks in advance if you offer assistance.

Posted by Mark W. on August 03, 2001 7:12 AM

Posted by Aladin Akyurek on August 03, 2001 8:24 AM

Another suggestion

Mark -- You got their before me, so I'll get maybe a "B".

Marvin gives the multiplication table as example, but I suspect he wants it for any table (I remember a post involving "Flights", but not sure about the poster).

Lets take another example. This data occupy A1:D4.

{0,"y1","y2","y3";"x1",1,1,3;"x2",2,3,2;"x3",1,2,1}

0 stands for blank. What is the value in the1st column that corresponds to (is in the same row as) 3 which is located in column that is labeled y2?

The trouble is that there may be more than one 3's in the target column (I'll come back to this issue later). What follow is a system of formulas (which can be molded into a single, mega-formula.

Select A1:D1 and name it ICOLS via the Name Box.
Select A2:A4 and name it IROWS.

Enter following labels in F1:F13.

{"StartRow";"EndRow";"EndCol";0;"Query";"Column Param";"Target Value";"Target Col";"Target Address";0;0;"Freq Target Value";"Index Value"}

Remember: 0 stands for blank.

In G1 enter: =ROW(2:2) [ or just a hard-coded 2 ]
In G2 enter: =COUNTA(A:A)+1 [ this formula requires that A to D should nothing but the table of interest ]
In G3 enter: =COLUMN(D:D) [ or just a hard-coded 4 ]
In G6 enter: y2 [ the label of the column which contains the target value ]
In G7 enter: 3 [ a target value we are interested in ]
In G8 enter: =MATCH(G6,ICOLS,0)