Excel formula for matching

Hi,

I need an excel formula. All my data is in column A, I have columns C, D and E that contain values (column B is irrelevant) I want to know if Column A exact value is present in column C,D or E.
Can anyone tell me the correct format, thanks,
LVL 6
Sid_FAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi Sid,

Depends what you mean - row-wise or globally?

See attached for examples of both.

Alan.
EE-29092860-Matches-Version1.xlsx
0
wellousCommented:
hello,
try  this nested  if:

=IF(A1=B1,"B",IF(A1=C1,"C",IF(A1=E1,"E","NOT found")))

Wellous
0
Ejgil HedegaardCommented:
You can use this for A2, copy down.
=COUNTIF(C:E,A2)
If result is not 0, the value is there.
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Shaun VermaakTechnical Specialist/DeveloperCommented:
Or building on Ejgil's comment
=IF(COUNTIF(C:E,A2)>0,"Duplicate","Unique")

Open in new window

0
Sid_FAuthor Commented:
Ok apologies but I need basic as possible here!
To reap I need to know what cell I click into to add the query. I assume the below query should work (or one of the other answers) so that if anything in ANY of the A column values is present in either C,D or E it will flag it in some way. The below formula didn't seem to work, thanks!

=IF(COUNTIF(C:E,A2)>0,"Duplicate","Unique")

A                                             B                             C                                              D                                         E
john@company.com          irrelevant data     Lance@company.com           josh@company.com      kate@mycompany.com
Paul@company.com          irrelevant data      Ted@company.com              john@company.com      jake@cmycompany.com
Ted@company.com           irrelevant data      Colin@company.com           eddie@company.com     paul@mycompany.com
0
AlanConsultantCommented:
Hi,

Try this in, say, F2 (or any column that works for you):

=IF(COUNTIF(A$2:A$9999,C2)+COUNTIF(A$2:A$9999,D2)+COUNTIF(A$2:A$9999,E2)>0,"Found","Not Found")

That will tell you whether any of the three items in C, D, and E are anywhere in column A.

I have used A2:A9999 - you can adjust that to whatever suits.


Does that provide solution?

Alan.
0
Ejgil HedegaardCommented:
You should get a match for john and Ted.
If not, you probably have some extra spaces in some of the texts.
See attached.
Duplicates.xlsx
0
Rob HensonFinance AnalystCommented:
With your names in column A and then remaining data in C to E, try this in column G (or wherever):

=IF(IFERROR("C"&MATCH($A2,$C:$C,0),"")
&IFERROR("D"&MATCH($A2,$D:$D,0),"")
&IFERROR("E"&MATCH($A2,$E:$E,0),"")="","Not Found",
IFERROR("C"&MATCH($A2,$C:$C,0),"")
&IFERROR("D"&MATCH($A2,$D:$D,0),"")
&IFERROR("E"&MATCH($A2,$E:$E,0),""))

The result will be the cell address of where the name is found.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlanConsultantCommented:
Appears to be solved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.