• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 61
  • Last Modified:

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,
0
Sid_F
Asked:
Sid_F
4 Solutions
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
AlanConsultantCommented:
Appears to be solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now