PDA

View Full Version : Match and Column Data

croeder
2011-06-10, 02:26 PM
How do I tell a match function to look in a certain row for data?

For example

1221 23 89
2213 81 22
9901 77 91

I want to be able to tell match to search the row with 2213 in it so I can determine which position or column 22 is in. I do have the ability to tell it what row it is in, can't figure out how to convert that into array data.....for example since 2213 is in row 2, can't figure how to make the array in match to be A2:F2. (fixed or defined arrays are out, move the data out, update and return frequently)

Thank you for the help!

JeffreyWeir
2011-06-10, 11:38 PM
.....for example since 2213 is in row 2, can't figure how to make the array in match to be A2:F2.

Can you clarify why the array would reach to F2? (Your example only goes to column C.) Are you trying to return a horizontal array from the matched row to the last column? (i.e. in the example above, if you have a match on 2213, do you then want to return all the numbers in that row i.e. {2213, 81, 22}? If so you could use this:
=INDEX(\$A:\$A,MATCH(\$F\$7,\$A:\$A,0)):INDEX(\$1:\$1048576,MATCH(\$F\$7,\$A:\$A,0),COUNTA(\$1:\$1))
...where the item you are looking up from column A is in F7.