naming rows and columns

[oaishm]

Hi

Is there a way to name rows and columns for references purposes. I'm not talking about setting sheet1!a:a = sheet1!name or using freeze panes to get the name of the column at the top. I mean if I get a table like

<TABLE>
<TBODY><TR>
<TD></TD>
<TD>a</TD>
<TD>b</TD>
<TD>c</TD>





</TR>
<TR>
<TD>1</TD>
<TD></TD>
<TD>larry</TD>
<TD>john</TD>





</TR>
<TR>
<TD>2</TD>
<TD>east</TD>
<TD>5</TD>
<TD>6</TD>





</TR>
<TR>
<TD>3</TD>
<TD>west</TD>
<TD>8</TD>
<TD>9</TD>





</TR>








</TABLE>

Rather than doing =c3 to get 9, I can do something with john and west to get 9.

There are things that don't work:

vlookup with separate lookup tables equating john with the number 3 like
vlookup("west",a1:c3,lookup("john",someotherarray),false) Because that takes a lifetime

a UDF since that requires for eaching through any array that you would assign the names of the columns and rows and there are hundreds of thousands of rows. I tried and it brought my computer to its knees. I think generally, a sheet with 20 or so udfs each of which for each through anything will kill the computer

Individually naming rows and columns. At least I don't know how.

The closest I've ever gotten is:
=SUM(('Monthly IS X'!LINEITEM=row_name)*(IF('Monthly IS X'!MONTH=column_name,'Monthly IS X'!DATA)))

But you'd have to set the data space, row and column for each individual sheet which of course, means resetting it if the shape of the data changes. Surely, even though this is Microsoft, there has to be a way, no?

 

[Jonmo1]

Try

=INDEX(B2:C3,MATCH("west",A2:A3,0),MATCH("john",B1:C1,0))

 

[oaishm]

Amazingly it worked. I can't imagine how the match function works, but I figure the fact that it's written in C can't be so much faster than a udf iterating through the worksheet, but it is. Here's the final formula

=INDEX(IS_2010A!$1:$1048576,MATCH($A24,IS_2010A!LINEITEM,0),MATCH(L$3,IS_2010A!MOYO,0))

Now if only there were a way to wrap these functions so they were user friendly to use.