Non-numeric row/column ordinal value for array

[lsgexel]

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?

 

[T. Valko]

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?

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

 

[T. Valko]

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

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.

 

[lsgexel]

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

 

[T. Valko]

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

You're welcome!

If you want to use cells to hold the criteria:

• A10 = N
• B10 = CM

Then the formula would be:

=INDIRECT(A10) INDIRECT(B10)

Note that the space is still there between the INDIRECT functions.

 

[Ron Coderre]

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.

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

 

[T. Valko]

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

Thanks, Ron!