Is this possible with Index/Match?

[RichardMGreen]

Hi all

I've got a table of data that looks like this:-

	        PCT
	Female	199
	Male	220
0-14	Unknown	1
	Female	202
	Male	111
15-24	Unknown	0

The table goes down in various age ranges but if I can get the first one working I should be OK sorting the rest.
I want to be able to use some sort of Index/Match to get the numbers for each age range for all 3 options but I'm struggling with it.

Anyone know if it's possible and, if so, how to do it?

 

[RoryA]

You'd need to use a straight INDEX/MATCH but for the Female and Male add 1 and 2 respectively to the MATCH result.

 

[RichardMGreen]

You mean an offset? I tried that, but sometimes, rows are missing (usually the unknown row).
If not, can you explain a little further please?

::edit::

One other thing, the age ranges are at the bottom of each section and not the top which may make things a bit more difficult.

 

[RoryA]

I mean something like:
=INDEX(C:C,match("0-14",A:A,0))
=INDEX(C:C,match("0-14",A:A,0)-1)
=INDEX(C:C,match("0-14",A:A,0)-2)

If you have missing data, it would be easier to just fill in the gaps in your first column and then use a lookup formula:
=LOOKUP(2,1/((A1:A1000="0-14")*(B1:B1000="Unknown")),C1:C1000)

 

[How_Do_I]

I'm not that good with xl so, hopefully somebody will tidy this up...

I had to move your ages but, what about this...


Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 18px"><COL style="WIDTH: 14px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>0-14</TD><TD>Female</TD><TD style="TEXT-ALIGN: right">199</TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00">Validation</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD>Male</TD><TD style="TEXT-ALIGN: right">200</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #ffff00">0-14</TD><TD>Female</TD><TD style="TEXT-ALIGN: right">199</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD>Unknown</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD>0-14</TD><TD>Male</TD><TD style="TEXT-ALIGN: right">200</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>15-24</TD><TD>Female</TD><TD style="TEXT-ALIGN: right">202</TD><TD> </TD><TD> </TD><TD>15-24</TD><TD>Unknown</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD>Male</TD><TD style="TEXT-ALIGN: right">111</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD>Unknown</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=MATCH(F2,$A$1:$A$6,0)</TD></TR><TR><TD>H2</TD><TD>=INDEX(INDIRECT("A"&$E$2):$C$6,MATCH(G2,$B$1:$B$6,0),3)</TD></TR><TR><TD>H3</TD><TD>=INDEX(INDIRECT("A"&$E$2):$C$6,MATCH(G3,$B$1:$B$6,0),3)</TD></TR><TR><TD>H4</TD><TD>=INDEX(INDIRECT("A"&$E$2):$C$6,MATCH(G4,$B$1:$B$6,0),3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #0000ff; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #0000ff; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #0000ff; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #0000ff"><TBODY><TR><TD>Data Validation in Spreadsheet</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Allow</TD><TD>Datas</TD><TD>Input 1</TD><TD>Input 2</TD></TR><TR><TD>F2</TD><TD>List</TD><TD> </TD><TD>=$F$3:$F$4</TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4