Row Lookups

tandy69

New Member
Joined
Oct 18, 2018
Messages
3
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):

NAMEMYMAILNEWMAILMail data1Mail data2Mail data 3>have about 50
Bob Smithbob.smith@mymail.com<Need to return the Newmail address here>bobs@othermail.combobs@newmail.comboby@hotmail.com
Larry SmithLarry.smith@mymail.com<Need to return the Newmail address here>larrys@somelese.comlarry@hotmail.comLarry@newmail.com
Sandy SmithSandy.Smith@mymail.com<Need to return the Newmail address here>sand@hotmail.comsandyb@outlook.comsandyb@newmail.com
<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> </tbody>

Any Ideas ?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi & welcome to MrExcel.
Try
=INDEX(D2:G2,1,MATCH("*newmail*",D2:G2,0))
 
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top