Original question by: Matt Goldstein
Question text: Don’t Know if I am asking this question correctly, but in regards to the Index match combo function: Instead of manually writing the column number at the end (in the ensuing example the number 2–> I.e =INDEX($A$2:$E$7, MATCH($G9, $E$2:$E$7, 0), 2) Can you also link to a column reference number and locking in the row–in other words putting a 1—2—3—4….n under the table in reference to the column number and then linking to that cell to make copying over the INDEX-MATCH formula quicker/automated/efficient I.e in this format: =INDEX($A$2:$E$7,MATCH($G9,$E$2:$E$7,0),A$8) –> in this example A$8 is linking all the way across to cell E8 with the numbers 1 thru 5 labelled in each row below the source table in the Exercise provided? I suppose there are a few ways to skin the cat…
Or may be use a second MATCH function for columns too. So you would have INDEX, MATCH, MATCH.
How does that sound, please?
Another option would be to use the COUNT function at the end. In your case =INDEX($A$2:$E$7,MATCH($G9,$E$2:$E$7,0),COUNT….)
Hope this helps!