MS Excel formula help

Hi all,

I remember several years ago that I used an MS Excel formula to access a single cell in a table using named ranges, but alas, I cannot remember what it is.
I think it is something like =(EF,RS) where EF is a named row and RS is a named column. Therefore the formula should access the cell containing 89. as in the table below:

.... PQ RS TU VW
AB 15 68 95 12
CD 45 78 37 28
EF 59 89 18 32
GH 38 46 49 52


The formula =sum(EF,RS) works but produces the result of 59+89+18+32(EF) plus 68+78+89+46(RS) totalling 479.
But what is the formula access only a single cell? Can anyone remind what the formula is?

 

Did you already look at this?

http://support.microsoft.com/kb/324861

They have some different commands there like lookup and index and a link to further information on finding data in different ways.

 

Thanks for the reply adding in the link, but I'm sure that it wasn't as complicated as reading the text on the link. I'm sure it was a very simple formula but was little used and not very widely known.

If you create a table as in my original message then name the rows and columns. Then type in =(EF,RS) without pressing <enter>, the ranges are highlighted to show that it is trying to access the cell.

As I said, it was something like =(EF,RS) but something is missing from this as it comes up with a #VALUE error!! =cells(EF,RS) doesn't work either....???

 

Try =INDEX(RangeName,RowNo,ColNo) where RowNo and ColNo are relative to top left cell of RangeName. This is from Excel 2007.

 

I think I was using MS Excel 2003 (v.11) SP3.

You have misunderstood my message. The AB.CD,EF,GH are row titles (range names) and PQ,RS,TU,VW are column titles (also ranges names).
To help you understand what I mean here is a table using perhaps common titles:

……..... North South East West
Person1.. 15.... 68.... 95.... 12
Person2.. 45.... 78.... 37.... 28
Person3.. 59.... 89.... 18.... 32
Person4.. 38.... 46.... 49.... 52

So South is a named column and Person3 is a named row.
So to get the value for Person3 in the South a formula which is similar to '=(Person3, South)' to produce 89 is used, but this doen't work as something is missing.

Yes, I understand what you are saying and this is purely using cell references such as =(B1) or =(AC23) or =(DF5) or =sum(C2,B4,F6) etc but I am using named ranges not cell references.

NB. I am not sure how to show a table in the message so I have padded out the spaces with dots.

 

Many thanks for the reply but yes this I know about but this wasn't it and what I am thinking. I know I have it on an Excel file somewhere but unfortunately I can't find it.
As I said, it is NOT a commonly known formula.

 

=INDEX(A1:E5,MATCH("Person3",A2:A5),MATCH("South",B2:E2))

Or I guess you could substitute:

=INDEX(South,MATCH("Person3",Names),1)

if South is the name of the column containing the desired value or

=INDEX(Person3,1,MATCH("South",Directions))

if Person3 is the name of the row.

Does that get you any closer?

 

Thanks for the reply cachehiker. Your suggested formula may work and I admit that I haven't tried it, but it's not the formula that I was thinking of.

I used to use the formula on various things but it's not really the sort of formula that is used often. I am (metaphorically) kicking myself that I cannot remember it, I seem to remember it was such a simple formula that it would've been difficult to forget - but alas.....!

As I mentioned, I am sure that I have got it in an Excel file somewhere, I suppose at the weekend I'll try to find it! It will come to me oneday!