Row Lookups

[tandy69]

Hi,

I am a bit stuck and was wondering if anyone has any ideas.

I have an extract from Exchange that has data similar to this..lots of rows but need a formula that searches each row for "*newmail.com" and appears in the NEWMAIL column (C):

[TABLE="width: 821"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2606;"> <col width="186" style="width: 140pt; mso-width-source: userset; mso-width-alt: 6493;"> <col width="293" style="width: 220pt; mso-width-source: userset; mso-width-alt: 10240;"> <col width="134" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4677;"> <col width="151" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5259;"> <col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5538;"> <col width="95" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3328;"> <tbody>[TR]
[TD="width: 75, bgcolor: transparent"]NAME[/TD]
[TD="width: 186, bgcolor: transparent"]MYMAIL[/TD]
[TD="width: 293, bgcolor: transparent"]NEWMAIL[/TD]
[TD="width: 134, bgcolor: transparent"]Mail data1[/TD]
[TD="width: 151, bgcolor: transparent"]Mail data2[/TD]
[TD="width: 159, bgcolor: transparent"]Mail data 3[/TD]
[TD="width: 95, bgcolor: transparent"]>have about 50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bob Smith[/TD]
[TD="bgcolor: transparent"]bob.smith@mymail.com[/TD]
[TD="bgcolor: transparent"]<Need to return the Newmail address here>[/TD]
[TD="bgcolor: transparent"]bobs@othermail.com[/TD]
[TD="bgcolor: transparent"]bobs@newmail.com[/TD]
[TD="bgcolor: transparent"]boby@hotmail.com[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Larry Smith[/TD]
[TD="bgcolor: transparent"]Larry.smith@mymail.com[/TD]
[TD="bgcolor: transparent"]<Need to return the Newmail address here>[/TD]
[TD="bgcolor: transparent"]larrys@somelese.com[/TD]
[TD="bgcolor: transparent"]larry@hotmail.com[/TD]
[TD="bgcolor: transparent"]Larry@newmail.com[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sandy Smith[/TD]
[TD="bgcolor: transparent"]Sandy.Smith@mymail.com[/TD]
[TD="bgcolor: transparent"]<Need to return the Newmail address here>[/TD]
[TD="bgcolor: transparent"]sand@hotmail.com[/TD]
[TD="bgcolor: transparent"]sandyb@outlook.com[/TD]
[TD="bgcolor: transparent"]sandyb@newmail.com[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Any Ideas ?

 

[Fluff]

Hi & welcome to MrExcel.
Try
=INDEX(D2:G2,1,MATCH("*newmail*",D2:G2,0))

 

[Firesword]

Hi tandy69
Assuming that the blank column for Bob Smith is C2 then try this
=ADDRESS(ROW(C2),MATCH("*"&"newmail.com",D2:AAA2,0)+3,4,TRUE)

Note- If Mail data1 is not in column D then you will need to change this reference. Also the +3 is how many columns are in front of the first reference. IE there are 3 columns in from of column D, if your starting reference is E then the formula would be:-
=ADDRESS(ROW(C2),MATCH("*"&"newmail.com",D2:AAA2,0)+4,4,TRUE)
you can go out as far as you need to. It will only find and report the fist cell it finds not all of them.

 

[tandy69]

THANK YOU BOTH !

Seems to work perfectly, so thanks for the help

T

 

[Fluff]

Glad we could help & thanks for the feedback