Look at column to see if there's an email address...

[thp510]

I have hundreds of rows of data in one column (Column A) that has either a name or an email address. I want to find a function that says IF there's an email address, do a vlookup to get the persons name (found on another table), otherwise don't do anything and just show me the name that's already listed in column A. ​Anyone have any advice? Thanks!

 

[Peter_SSs]

Any chance of a small set of dummy sample data and expected results?

 

[Peter_SSs]

Actually, is this what you want?

Formula copied down.

Excel Workbook
	A	B	C	D	E
1
2	Name 1	Name 1		abc@def.com	Name 101
3	Name 2	Name 2		jjj@kkk.sss	Name 102
4	Name 3	Name 3		tom.jones@somewhere.com.uk	Name 103
5	abc@def.com	Name 101
6	Name 4	Name 4
7	Name 5	Name 5
8	tom.jones@somewhere.com.uk	Name 103
Lookup name

 

[Kamolga]

Where A1 is the cell you are looking for

=if(ISNUMBER(SEARCH("@",A1))=true,vlookup(A1,[COLOR=#ff0000]tabble_array,column,[/COLOR]FALSE),A1)

 

[thp510]

YES! This looks like it worked. Thanks!

Actually, is this what you want?

Spreadsheet Formulas

Cell Formula B2 =IFERROR(VLOOKUP(A2,$D$2:$E$4,2,0),A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Legacy 421546]

Can you use VBA? If so, it's easy

 

[thp510]

Where A1 is the cell you are looking for

=if(ISNUMBER(SEARCH("@",A1))=true,vlookup(A1,[COLOR=#ff0000]tabble_array,column,[/COLOR]FALSE),A1)

Hi Kamolga, I tried this and I'm getting an error. I even tried

=if(ISNUMBER(SEARCH("@",A1))=true, "Yes- True","Nope - False")

[/QUOTE]

I even did this:

=if(ISNUMBER(FIND("@",A1))=true, "Yes- True","Nope - False")

[/QUOTE]

Still just get the #N/A error response. Is '=TRUE' a valid command? Of note, I'm using Google sheets for now.

 

[Kamolga]

I know nothing about google sheets but in excel instead of table array, you have to put the cells where the table is, like $C$1:$D$100 (or a name if you named the range) and instead of column, the number of the column in the table where the output is (the input is always on first), so 2 if the address is in C and the name is in D.

[LEFT][FONT=monospace][LEFT][COLOR=#333333][FONT=monospace][I]=if(ISNUMBER(SEARCH("@",A1))=true,vlookup(A1,[/I][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]$C$1:$D$[/FONT][/COLOR]100[I][COLOR=#ff0000][FONT=monospace],2,[/FONT][/COLOR][FONT=monospace]F[/FONT][COLOR=#333333][FONT=monospace]ALSE),A1)[/FONT][/COLOR][/I][/LEFT]
[/FONT][/LEFT]

 

[Peter_SSs]

YES! This looks like it worked. Thanks!

You're welcome. Thanks for letting us know.

I'm using Google sheets for now.

If you post further questions you should state this clearly up-front. Since this is an Excel forum, that is what helpers are expecting to help with.