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)
In G9 enter: =ADDRESS(G1,G8)&":"&ADDRESS(G2,G8)
In G12 enter: =COUNTIF(INDIRECT(G9),G7)
In G13 enter: =IF(G12=1,INDEX(IROWS,MATCH(G7,INDIRECT(G9),0)),"")
The last formula produces the desired result.
If the intermediate result in G12 is greater than 1 (that is the issue I mentioned at the start), you need to expand this set of formulas in order to get corresponding first column values for all instances of the target value. At this point you have the uncertainty that goes with the fact that the target value is not unique (By the way, that is the reason why I declined to reply to the question involving flights.)
The above system of formulas should also work with queries involving the multiplication table.
If interested in the workbook, just send me an email.
Hope I'll get that B. :-)
Aladin
