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