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

# 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
4 Solutions

ConsultantCommented:
Hi Sid,

Depends what you mean - row-wise or globally?

See attached for examples of both.

Alan.
EE-29092860-Matches-Version1.xlsx
0

Commented:
hello,
try  this nested  if:

Wellous
0

Commented:
You can use this for A2, copy down.
=COUNTIF(C:E,A2)
If result is not 0, the value is there.
1

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

Author 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

ConsultantCommented:
Hi,

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

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

Commented:
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

Finance 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("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

ConsultantCommented:
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.