Remind me... checking 2 lists against each other

Remind me... checking 2 lists against each other

Didn't find your answer?

Basically I have a list of our clients which I will make into 'A Smith' format.  I also have a list of clients per HMRC which I can again make into 'A Smith' without too much hassle.

I used to be able to make a formula that said 'check column B for a match to A1 and say yes or no'.  Given A Smith might be in A1 on our list and B12 on HMRCs it needs to check the entire column.

Help?

Replies (5)

Please login or register to join the discussion.

avatar
By Derek Henderson
08th Feb 2011 12:00

Comparing two columns of names

Hi CC,

I assume you are using Excel? If so than this should do the trick:

=IF(ISERROR(VLOOKUP(B2,A$2:A$500,1,)),"No","Yes")

This assumes that you are looking for the Column B data and seeing if it exists in Column A. Make sure you input the $ before the row numbers as above to fix the lookup range.

 

Regards,

Derek

Thanks (0)
avatar
By thisistibi
08th Feb 2011 12:33

A simpler way

Whilst I'm sure the above forumla works, there is a simpler way:

a) =A1=A2

Assuming your data is in cells A1 and A2.  The simple formula will return "TRUE" if they match or "FALSE" if they don't.

b) =IF(A1=A2,"Yes","No")

Will give Yes & No, rather than True & False.

Thanks (0)
Quack
By Constantly Confused
08th Feb 2011 12:43

Thanks

That worked great!

#Edit#

thisistibi - that would be great except as I say my two Mr A Smith's are on different lines as all it takes is one client to be on only one list and the rest of the column will also be out of line.

Thanks (0)
avatar
By paulwakefield1
08th Feb 2011 12:44

Vlookup

If Derek's answer is amended to:

=IF(ISERROR(VLOOKUP(B2,A$2:A$500,1,False)),"No","Yes")

then this will ensure an exact match.

I'm not sure thisistibi's solution will achieve what the OP is looking for.

MATCH is another function that could be used instead of VLOOKUP.

Thanks (0)
avatar
By thisistibi
08th Feb 2011 12:54

Oops

Sorry, my fault, I didn't read the whole question.

Thanks (0)