M
MH
Trying to reference the data in a cell that matches Both a column heading and
row heading.
row heading.
P
Peo Sjoblom
I am not use what you mean, maybe this?
http://www.contextures.com/xlFunctions03.html#IndexMatch2
--
Regards,
Peo Sjoblom
http://www.contextures.com/xlFunctions03.html#IndexMatch2
--
Regards,
Peo Sjoblom
M
MH
Thank you for referencing that website. Great resource!!
C
Chip Pearson
You can use a formula similar to the following:
=OFFSET($B$4,MATCH("z",B5:B8,0),MATCH("c",C4:F4,0),1,1)
This assumes that the row headings are in B5:B8 and the column
headings are in C4:F4. The formula looks for a row header of "z" and a
column header of "c". Change to suit your needs. Cell B4 is where the
row and column headers intersect. While the formula uses B4 as the
anchor for the OFFSET function, the contents (if any) of B4 are
entirely irrelevant. If a row and/or column header is not found, the
formula returns a #N/A error.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
=OFFSET($B$4,MATCH("z",B5:B8,0),MATCH("c",C4:F4,0),1,1)
This assumes that the row headings are in B5:B8 and the column
headings are in C4:F4. The formula looks for a row header of "z" and a
column header of "c". Change to suit your needs. Cell B4 is where the
row and column headers intersect. While the formula uses B4 as the
anchor for the OFFSET function, the contents (if any) of B4 are
entirely irrelevant. If a row and/or column header is not found, the
formula returns a #N/A error.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
S
ShaneDevenshire
Hi,
As well as OFFSET, we commonly use INDEX:
=INDEX(B2
10,MATCH(F1,B11,),MATCH(G1,A2:A10,))
As well as OFFSET, we commonly use INDEX:
=INDEX(B2
10,MATCH(F1,B11,),MATCH(G1,A2:A10,))