Comparing list

  • I have a simple comparing question. I know you can use the vlookup formula, however I don't know how to use it. My problem is I have about 100 entries is column A and about 500 entries in column B. I want to compare A to B and find out which entries in A don't match column B. So if out of my 100 in column A, say 98 match in column B, I need to know which 2 didn't. Any help in simplifing how to write the formula is appreciated. Thanks

  • Re: Comparing list


    =MATCH(A1,$B$1:$B$500,0)


    will return #N/A if there is no match. If there is a match, it returns the position in column B of the corresponding entry in column A.


    HTH


    Richard

    If absence makes the heart grow fonder, and familiarity breeds contempt, perhaps my wife should live in Darwin?

  • Re: Comparing list


    Hi!


    Put this in C1 and copy down in as many rows as you have entries in _Column A._


    =IF(ISERROR(VLOOKUP(A1,$B$1:$B$500,1,)),"not found","")


    If your data is not in A1/B1 change the numbers to A2 and $B$2 or whatever number you need and change the $B$500 to the extent of your list in Column B.


    Vlookup is simply VLOOKUP('what_to_look_for' , 'where_to_look', which_column_to_look_in,)


    The ISERROR() function traps the #N/A response if Vlookup doesn't find a match.


    and the IF function says if not found (#N/A) display the message 'not found'. Otherwise, if found, display "" (nothing) because we don't care!


    Cheers,


    dr

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!