Non-numeric row/column ordinal value for array

lsgexel said:

I have the following table in Excel:
--------------------------------------------
A B C D E
--------------------------------------------
1 Health Plan
2 Region MC MD CM SL
3 N .5 .7 .9 1.2
4 E .6 1.4 5.3 3.6
--------------------------------------------
I would like to use non-numeric values for rows and columns to index the table as follows:

=Index(B3:E4,N,CM)
should return the value .9

How can this be done?

Click to expand...

T. Valko said:

What version of Excel are you using?

Here's how to do it in Excel 2007.

Let's assume your table is in the range A1:E3 (includes both row and column headers).

Select the range A1:E3
Goto the Formulas tab
Defined Names
Create from selection
Select: Top Row and Left Column
OK out

Then, the formula would be:

=N CM

You just type in the row and column headers separated by a space.

=E MD

will return 1.4

Click to expand...

lsgexel said:

I am using Excel 2007 and you suggestion works. Thanks for the quick response.

Click to expand...

• A10 = N
• B10 = CM

T. Valko said:

P.S.

I think I read somewhere that this feature was removed from Excel 2010?

Can anyone verify that? I don't have Excel 2010 to test it.

Click to expand...

Ron Coderre said:

1) It still works in Excel 2010
2) It's really only practical with small ranges.

Click to expand...