Hi,
I have a table of about 30 columns by 50 rows. Each cell has a unique text value in it. In a separate column to the left of the table, is a list of other text values.
(In case you're interested, this is because each row in the table contains multiple different spellings of the same **** thing, and I am trying to somehow merge them all into one. The trouble is that some of the variations may in time turn out to be significant so I can't just find-replace the lot of them)
I would like to have a formula in a different part of the worksheet that looks at the value of a cell (whose text value will definitely also exist in the 30x50 table) and returns the row number of its counterpart in the table.
With this row number I can use INDIRECT to reference the value in the column running down the left of the table.
It is just like the MATCH function, but needs to work on a 2D table and just return the row number.
Can anyone help? Or is the only way to write a macro to perform the operation?
I have a table of about 30 columns by 50 rows. Each cell has a unique text value in it. In a separate column to the left of the table, is a list of other text values.
(In case you're interested, this is because each row in the table contains multiple different spellings of the same **** thing, and I am trying to somehow merge them all into one. The trouble is that some of the variations may in time turn out to be significant so I can't just find-replace the lot of them)
I would like to have a formula in a different part of the worksheet that looks at the value of a cell (whose text value will definitely also exist in the 30x50 table) and returns the row number of its counterpart in the table.
With this row number I can use INDIRECT to reference the value in the column running down the left of the table.
It is just like the MATCH function, but needs to work on a 2D table and just return the row number.
Can anyone help? Or is the only way to write a macro to perform the operation?
