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

#### Eamonn100

##### Board Regular
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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### BarryL

##### Well-known Member
="Aaron "&INDEX(B:B,MATCH("Aaron",A:A,0))

#### Eric W

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
Here are a couple alternatives:

ABCDEFGHI
1NameValueAaron65NameValues
2Paul25Max25
3John6865
4Mike95
5Billy78
6Max65
7Max25
8Paul54
9John78
10Aaron65
11Paul23
12Mike11
13

</tbody>
Sheet13

Worksheet Formulas
CellFormula
F1=SUMIF(\$A:\$A,E1,\$B:\$B)

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
I2{=IF(I1="","",IFERROR(SMALL(IF(\$A\$2:\$A\$12=\$H\$2,\$B\$2:\$B\$12),ROWS(\$I\$2:\$I2)),""))}

</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

##### Board Regular
Here are a couple alternatives:

ABCDEFGHI
1NameValueAaron65NameValues
2Paul25Max25
3John6865
4Mike95
5Billy78
6Max65
7Max25
8Paul54
9John78
10Aaron65
11Paul23
12Mike11
13

<tbody>
</tbody>
Sheet13

Worksheet Formulas
CellFormula
F1=SUMIF(\$A:\$A,E1,\$B:\$B)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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.

opps!

Last edited:

#### Eamonn100

##### Board Regular
="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

##### Board Regular
 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--><!----------------------------->
 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.

Replies
3
Views
139
Replies
0
Views
486
Replies
5
Views
165
Replies
7
Views
315
Replies
7
Views
175

1,191,281
Messages
5,985,735
Members
439,978
Latest member
Mr930R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back