Cell lookup formula help

[lewis_blackburn]

Could someone please help me with what seems like should be an easy formula? I have a range of cells with row and column headings and I want to lookup a cell in that range by those headings. For example, for the range that look like this:

<TABLE><TBODY><TR><TD></TD><TD>Jan</TD><TD>Feb</TD><TD>Mar</TD></TR><TR><TD>Stock</TD><TD>100</TD><TD>300</TD><TD>200</TD></TR><TR><TD>Bond</TD><TD>110</TD><TD>330</TD><TD>220</TD></TR><TR><TD>Cash</TD><TD>140</TD><TD>360</TD><TD>250</TD></TR></TBODY></TABLE>

I want a function to get me of the value of the cell with the column heading "Mar" and the row heading "Bond". My first attempt was to use the SUMIFS function like this...

=SUMIFS(B2:D4, B1:D1, "Mar", A2:A4, "Bond")

...where the correct answer would be 220 but that is not working. Can someone please point me in the right direction?

Thanks!

 

[VoG]

Try

=INDEX(A1:D4,MATCH("Bond",A1:A4,0),MATCH("Mar",A1:D1,0))

 

[Jonmo1]

Try Index/Match

Formula in A10 is
=INDEX(B2:D4,MATCH(A8,A2:A4,0),MATCH(A7,B1:D1,0))

A7 = Mar
A8 = Bond

See below..

Hope that helps.

Excel Workbook
	A	B	C	D
1		Jan	Feb	Mar
2	Stock	100	300	200
3	Bond	110	330	220
4	Cash	140	360	250
5
6
7	Mar
8	Bond
9
10	220
Sheet1

 

[p45cal]

You could also use a formula such as:
=Bond Mar
but first you have to use the Create Names from selection dialogue:
Select the table including headers top and left, then
In Excel 2007-2010:
Formulas Ribbon, Create from selection
In earlier versions:
from the menu at the top: Insert|Name|Create…

The rest is the same in both versions:
Check the checkboxes labelled Top Row and Left Column, and click OK.
That's it.

In version prior to xl2007, you can do it without creating Names! Go to
Tools|Options, Calculation Tab, check the 'Accept labels in formulas' chaeckbox, and use th same formula.