Searching for numbers in one workbook & marking the number in another workbook.

  • Hello.


    I have two workbooks, workbookA and workbookB.

    In the workbookA there are columns J & K which contain phone numbers. Approximately 1800 numbers.


    What I'm trying to do is search for the numbers of column J & K in workbookB.
    If the numbers exist in workbookB, mark them down with an X on the same row in column U of workbookB.

  • Re: Searching for numbers in one workbook & marking the number in another workbook.


    I can't give you the exact syntax without having more detail. However, using the Match function to see if see if number is there, the isError function to see if it returns an error, and the IF function to make the X. So something like
    =IF(ISERROR(MATCH(J2,[WorkbookB]Sheet1!$J$2:$J$1200,0)),"","X")
    after you adjust the reference

  • Re: Searching for numbers in one workbook & marking the number in another workbook.


    I have cut out parts of the woorkbooks just so it's more clear.


    ozgrid.com/forum/core/index.php?attachment/62267/
    and
    ozgrid.com/forum/core/index.php?attachment/62268/


    As you can see the number in column J of woorkbookA has to be checked with column S of woorkbookB.
    If the number in column J exists in column S of woorkbookB, I want it to be marked with an X in column U of workbookB.


    I hope this extra information helps.

  • Re: Searching for numbers in one workbook & marking the number in another workbook.


    Put this formula in U2 of woorbookB and drag down (changing the 13 to as lrge a number as needed).
    =IF(ISERROR(MATCH(S2,[woorkbookA.xlsm]Blad1!$J$1:$J$13,0)),"","x")

  • Re: Searching for numbers in one workbook & marking the number in another workbook.


    What should I do if I keep getting "#NAME?" errors when I put the formula in U2? Is there something simple that I have missed?
    I really appreciate your help Derk :)

  • Re: Searching for numbers in one workbook & marking the number in another workbook.


    Try this:


    =IF(ISERROR(MATCH(S2;[woorkbookA.xlsm]Blad1!$J$1:$J$13;0));"";"x")


    maybe your regional settings.