# 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
###### Who is Participating?

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.

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

Experts Exchange Solution brought to you by