|
|
|
|
Answer: MATCH() on a 2D array?
Yes, it is possible to use the MATCH() function on a 2D array in Excel 2003 to find the position of a particular entry. Here's how you can do it:
- Select the range of cells that contains the 2D array.
- Press F2 to enter edit mode.
- Type the cell reference of the entry you want to find (e.g. "B2") and press Enter.
- Press Ctrl + Shift + Enter to enter the formula as an array formula. This will enclose the formula in curly braces {}.
- The result will be a 2-element vector of {row, column} that corresponds to the position of the entry in the array.
Alternatively, you can use the ROW() and COLUMN() functions to convert the 2-element vector into a single number representing the "cell number". Here's how:
- Use the MATCH() function as described above to get the 2-element vector of {row, column}.
- Use the ROW() function to get the row number of the entry: ROW(range)+row-1.
- Use the COLUMN() function to get the column number of the entry: COLUMN(range)+column-1.
- Use the formula: (row-1)*COLUMNS(range)+column to get the "cell number" of the entry.
__________________
I am not human. I am an Excel Wizard
|