Excel vlookup inside cell with CSV

Question

0.00/5 (No votes)

See more:

Excel-2016

Hello, I have a ReportOwnerQuery Sheet with a list of emails in with Column C being seperated by commas like "emailone@email.com, emailtwo@email.com". I need to Vlookup or a similar method to check to see if one of the emails in column C of ReportOwnerQuery is on the OwnerList sheet which contains owner emails in A2:A60. If one of those emails is an owner email for the cell then place in in Column D of ReportOwnerQuery. If there is a VBA for this as well that could help I would gladly use that too.

What I have tried:

In cell D2 of ReportOwnerQuery: =VLOOKUP(&"*",OwnerList!A1:A1000,1,FALSE) returns a #N/A
In cell D2 of ReportOwnerQuery: =VLOOKUP(&"*",OwnerList!A1:A1000,1,TRUE) returns an incorrect email from OwnerList

Posted 15-Apr-19 7:44am

Trogers96

Updated 16-Apr-19 3:32am

Maciej Los

v3

Add a Solution

Comments

RedDk 15-Apr-19 14:17pm

How many worksheets does this CSV contain? Can you save this .csv? And when you get a prompt when you save this .csv telling you that some functionality might be incompatible if you try, do you save it as .xlsm instead?

Trogers96 15-Apr-19 14:26pm

I meant that only column C is in a CSV type format. I improved my question to hopefully add more clarification.

Patrice T 16-Apr-19 9:11am

a csv contain 1 worksheet. it is a flat list of raw data.

2 solutions

Solution 1

Check my past answer: Excel VBA vlookup with multiple results[^]

Posted 15-Apr-19 22:06pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Accepted Answer · 2019-04-16T03:32:00

Quote:
In cell D2 of ReportOwnerQuery: =VLOOKUP(&"*",OwnerList!A1:A1000,1,FALSE) returns a #N/A
In cell D2 of ReportOwnerQuery: =VLOOKUP(&"*",OwnerList!A1:A1000,1,TRUE) returns an incorrect email from OwnerList

This is exactly the expected behavior for VLOOKUP.

Quote:
If one of those emails is an owner email for the cell then place in in Column D of ReportOwnerQuery.

Yes but what to put in cell when there is no match ?
Because the behavior you describe is the one when the email address is not in the list.
I would try something like:

VB

=IF(ISERROR(VLOOKUP(&"*",OwnerList!A1:A1000,1,FALSE)),your result,your result)
or 
=IFERROR(VLOOKUP(&"*",OwnerList!A1:A1000,1,FALSE),your result)