Finding certain text in a column and the cell next to it.

[Eamonn100]

Hi,

On my Excel sheet I have column A full of text. I need a formula that finds/references Aaron in column A (and what ever value is in the cell next to the Aaron in column B). Aaron isn't always in the same cell and can be anywhere in column A. If the figure beside Aaron needs to go into a cell of it's own that's ok, (e.g. Formula cells E1, F1).

	A	B	C	D	E	F
1	Paul	25			Aaron	65
2	John	68
3	Mike	95
4	Billy	78
5	Max	65
6	Max	25
7	Paul	54
8	John	78
9	Aaron	65
10	Paul	23
11	Mike	11

<colgroup><col width="64" style="width:48pt"> <col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" style="width:48pt"> <col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks.

 

[lrobbo314]

<style id="Book1_13027_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1513027 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6313027 {color:#574123; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Tahoma, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:middle; border:.5pt solid windowtext; background:white; mso-pattern:black none; white-space:normal; padding-left:18px; mso-char-indent-count:2;}.xl6413027 {color:#574123; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Tahoma, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:right; vertical-align:middle; border:.5pt solid windowtext; background:white; mso-pattern:black none; white-space:normal; padding-right:18px; mso-char-indent-count:2;}.xl6513027 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; background:white; mso-pattern:black none; white-space:nowrap;}.xl6613027 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Book1_13027" align=center xublishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=358 style='border-collapse: collapse;table-layout:fixed;width:269pt'> <col width=35 style='mso-width-source:userset;mso-width-alt:1280;width:26pt'> <col width=77 style='mso-width-source:userset;mso-width-alt:2816;width:58pt'> <col width=55 style='mso-width-source:userset;mso-width-alt:2011;width:41pt'> <col width=29 style='mso-width-source:userset;mso-width-alt:1060;width:22pt'> <col width=30 style='mso-width-source:userset;mso-width-alt:1097;width:23pt'> <col width=77 style='mso-width-source:userset;mso-width-alt:2816;width:58pt'> <col width=55 style='mso-width-source:userset;mso-width-alt:2011;width:41pt'> <tr height=20 style='height:15.0pt'> <td height=20 class=xl1513027 width=35 style='height:15.0pt;width:26pt'></td> <td class=xl6613027 width=77 style='width:58pt'>A</td> <td class=xl6613027 width=55 style='width:41pt'>B</td> <td class=xl6613027 width=29 style='width:22pt'>C</td> <td class=xl6613027 width=30 style='width:23pt'>D</td> <td class=xl6613027 width=77 style='width:58pt'>E</td> <td class=xl6613027 width=55 style='width:41pt'>F</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>1</td> <td class=xl6313027 width=77 style='width:58pt'>Paul</td> <td class=xl6413027 width=55 style='border-left:none;width:41pt'>25</td> <td class=xl6313027 width=29 style='border-left:none;width:22pt'> </td> <td class=xl6313027 width=30 style='border-left:none;width:23pt'> </td> <td class=xl6313027 width=77 style='border-left:none;width:58pt'>Aaron</td> <td class=xl6413027 width=55 style='border-left:none;width:41pt'>65</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>2</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>John</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>68</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>3</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Mike</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>95</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>4</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Billy</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>78</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>5</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Max</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>65</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>6</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Max</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>25</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>7</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Paul</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>54</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>8</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>John</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>78</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>9</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Aaron</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>65</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>10</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Paul</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>23</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6313027 width=55 style='border-top:none;border-left:none; width:41pt'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6613027 style='height:15.0pt'>11</td> <td class=xl6313027 width=77 style='border-top:none;width:58pt'>Mike</td> <td class=xl6413027 width=55 style='border-top:none;border-left:none; width:41pt'>11</td> <td class=xl6313027 width=29 style='border-top:none;border-left:none; width:22pt'> </td> <td class=xl6313027 width=30 style='border-top:none;border-left:none; width:23pt'> </td> <td class=xl6313027 width=77 style='border-top:none;border-left:none; width:58pt'> </td> <td class=xl6513027 style='border-top:none;border-left:none'> </td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=35 style='width:26pt'></td> <td width=77 style='width:58pt'></td> <td width=55 style='width:41pt'></td> <td width=29 style='width:22pt'></td> <td width=30 style='width:23pt'></td> <td width=77 style='width:58pt'></td> <td width=55 style='width:41pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>
<style id="Book1_13031_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1513031 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6313031 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6413031 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Book1_13031" align=center xublishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=332 style='border-collapse: collapse;table-layout:fixed;width:249pt'> <col width=49 style='mso-width-source:userset;mso-width-alt:1792;width:37pt'> <col width=283 style='mso-width-source:userset;mso-width-alt:10349;width:212pt'> <tr height=20 style='height:15.0pt'> <td colspan=2 height=20 class=xl6313031 width=332 style='height:15.0pt; width:249pt'>Formulas</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6413031 style='height:15.0pt;border-top:none'>$F$1</td> <td class=xl6413031 style='border-top:none;border-left:none'>=INDEX($B$1:$B$11,MATCH(E1,A1:A11,0))</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=49 style='width:37pt'></td> <td width=283 style='width:212pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>

 

[BarryL]

="Aaron "&INDEX(B:B,MATCH("Aaron",A:A,0))

 

[Eric W]

A couple of questions:

Is the value in column B always going to be numeric?

What do you want to see if the name occurs more than once, like Max?

 

[Eamonn100]

A couple of questions:

Is the value in column B always going to be numeric?

What do you want to see if the name occurs more than once, like Max?

Hi Eric,

yes the value in column B is always numeric. The name will only occur once.

Thanks.

P.S.

If you want to through a second formula at me for if the name occurs more than once that would be good too.

 

[Eric W]

Here are a couple alternatives:

	A	B	C	D	E	F	G	H	I
1	Name	Value			Aaron	65		Name	Values
2	Paul	25						Max	25
3	John	68							65
4	Mike	95
5	Billy	78
6	Max	65
7	Max	25
8	Paul	54
9	John	78
10	Aaron	65
11	Paul	23
12	Mike	11
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula F1 =SUMIF($A:$A,E1,$B:$B) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I2 {=IF(I1="","",IFERROR(SMALL(IF($A$2:$A$12=$H$2,$B$2:$B$12),ROWS($I$2:$I2)),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula in F1 is an alternative to BarryL and lrobbo's formulas. A bit shorter, but it only works if column B is numeric. If the name occurs twice, it will return the sum of the values, e.g. 90 if you put Max in E1.

If you want an actual list of the values, you can use the formula in I2. The header in I1 is required. Put the name you want in H2. Put the formula in I2, change the references to match your sheet, and confirm with Control+Shift+Enter. Copy down as far as needed. It will return the values in sorted order. This also only works for numeric values, but it can be changed to handle text as well.

Hope this helps!

 

[Eamonn100]

Here are a couple alternatives:

	A	B	C	D	E	F	G	H	I
1	Name	Value			Aaron	65		Name	Values
2	Paul	25						Max	25
3	John	68							65
4	Mike	95
5	Billy	78
6	Max	65
7	Max	25
8	Paul	54
9	John	78
10	Aaron	65
11	Paul	23
12	Mike	11
13

<tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula F1 =SUMIF($A:$A,E1,$B:$B) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I2 {=IF(I1="","",IFERROR(SMALL(IF($A$2:$A$12=$H$2,$B$2:$B$12),ROWS($I$2:$I2)),""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula in F1 is an alternative to BarryL and lrobbo's formulas. A bit shorter, but it only works if column B is numeric. If the name occurs twice, it will return the sum of the values, e.g. 90 if you put Max in E1.

If you want an actual list of the values, you can use the formula in I2. The header in I1 is required. Put the name you want in H2. Put the formula in I2, change the references to match your sheet, and confirm with Control+Shift+Enter. Copy down as far as needed. It will return the values in sorted order. This also only works for numeric values, but it can be changed to handle text as well.

Hope this helps!

Cool that double Max calculation formula has gave me a idea for something else. I'll have a play about but I think I'll go with Barry's.

 

[Eamonn100]

opps!

 

[Eamonn100]

="Aaron "&INDEX(B:B,MATCH("Aaron",A:A,0))

Hi Barry,


I think I'll use yours. Could you write the formula so that it gives the B cell value in F1 and not all together in E1.


Thanks.

 

[Eamonn100]

<style id="Book1_13027_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1513027 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6313027 {color:#574123; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Tahoma, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:middle; border:.5pt solid windowtext; background:white; mso-pattern:black none; white-space:normal; padding-left:18px; mso-char-indent-count:2;}.xl6413027 {color:#574123; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Tahoma, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:right; vertical-align:middle; border:.5pt solid windowtext; background:white; mso-pattern:black none; white-space:normal; padding-right:18px; mso-char-indent-count:2;}.xl6513027 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; background:white; mso-pattern:black none; white-space:nowrap;}.xl6613027 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style>******><!--[if !excel]> <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!----------------------------->

	A	B	C	D	E	F
1	Paul	25			Aaron	65
2	John	68
3	Mike	95
4	Billy	78
5	Max	65
6	Max	25
7	Paul	54
8	John	78
9	Aaron	65
10	Paul	23
11	Mike	11

<colgroup><col width="35" style="mso-width-source:userset;mso-width-alt:1280;width:26pt"> <col width="77" style="mso-width-source:userset;mso-width-alt:2816;width:58pt"> <col width="55" style="mso-width-source:userset;mso-width-alt:2011;width:41pt"> <col width="29" style="mso-width-source:userset;mso-width-alt:1060;width:22pt"> <col width="30" style="mso-width-source:userset;mso-width-alt:1097;width:23pt"> <col width="77" style="mso-width-source:userset;mso-width-alt:2816;width:58pt"> <col width="55" style="mso-width-source:userset;mso-width-alt:2011;width:41pt"> </colgroup><tbody>
<!--[if supportMisalignedColumns]-->
<!--[endif]--></tbody>

<!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!----------------------------->
<style id="Book1_13031_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1513031 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6313031 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6413031 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style>******><!--[if !excel]> <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!----------------------------->

Formulas
$F$1	=INDEX($B$1:$B$11,MATCH(E1,A1:A11,0))

<colgroup><col width="49" style="mso-width-source:userset;mso-width-alt:1792;width:37pt"> <col width="283" style="mso-width-source:userset;mso-width-alt:10349;width:212pt"> </colgroup><tbody>
<!--[if supportMisalignedColumns]-->
<!--[endif]--></tbody>

<!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!----------------------------->

Hi lrobbo,

when I try and paste your formulas into the sheet. It doesn't work. Could you tell me where to paste the above two formulas so that I'm sure I'm not making a mistake.