Ok so I've been racking my brain over this and cannot for the life of me figure it out. I am trying to achieve the following; Lets say I have the following values.

A:1 Blue D:1 Blue

A:2 Green D:2 Green

A:3 Purple D:3 White

A:4 Pink D:4 Yellow

A:5 Yellow D:5 Black

A:6 Orange D:6 Indigo

A:7 Green

A:8 Pink

A:9 Yellow

A:10 Indigo

A:11 Green

A:12 Black

A:13 WhiteWhat formula would I use If was trying to determine if the Values in column A MATCHED ANY of the Values in Column D. Further to this, if Column A did match any value in Column D then a Yes or No would appear in the cell next to the corresponding value in Column A (i.e. Column B)

Not sure if I am getting across what I'm trying to do...

Thanks guys - appreciate any help

A posting tip: In the future, try to use a subject line that is relevant to the contents of your post. If everyone used a generic subject line such as "Excel Formula Help!", we wouldn't be able to tell one question from another. Browse through the other threads in this forum and look at the types of subject lines that others have used.

I have edited the subject line of this thread.

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You could try the COUNTIF function as follows: Enter this in B1 and drag it down:

=IF(COUNTIF($D$1:$D$6,A1)>0,"Yes","No")

You could also use ISNA and VLOOKUP:

=IF(ISNA(VLOOKUP(A1,$D$1:$D$6,1,0)),"No","Yes")

If VLOOKUP returns a #N/A error, then the value wasn't found. The ISNA function checks for that error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad03 Thanks for the solution and the Subject Tip!

Will keep that in mind when posting in the future.

I'll let you know whether or not the solution worked.

Cheers

Thanks DerbyDad03 - it worked like a charm. I can now manipulate the data using datapilot as well. Thanks again

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History