VBA to perform a search from one file to another.

I need to search a file that has over 200,000 rows. If the specific reference in my file is on the other file I need the macro to put Yes in my file and if its not there to put No.

So..... the file is in the path G:\Clients\Open Invoice\Open Invoice - UPDATED.xlsx

My file [the one with the Macro] needs to look up the Reference in C5 [on tab called PD3] and see if this is on the file ‘Open Invoice - UPDATED.xlsx’. If the Ref is found enter 'Yes' in my file [on tab called PD3] cell J6 and if not found put 'No'
Who is Participating?
regmigrantConnect With a Mentor Commented:
You don't need a macro just a vlookup:-

in D5 (the column you want yes or no in) put:
=If(iserror(VLOOKUP($C5,'[G:\Clients\Open Invoice\Open Invoice - UPDATED.xlsx]Sheet1'!$A:$A,1,0)),"No","Yes")

- you may need to change the 'sheet1' to refer to specific sheet and the range $A:$A to refer to the appropriate column

Copy the formula down all of the rows in your target spreadsheet

JagwarmanAuthor Commented:
regmigrant bizzarley I could not get this to work but, I changed iserror to ISNA and it does work. Fingers crossed that it will always return the correct vallue.
iserror should work (for excel 2003 and up) but hopefully you will be ok with ISNA - good luck
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.