I have a reference spreadsheet of names and DOBs that rarely changes. Column "A" is person name and "B" is DOB. The spreadsheet has a single page named "Sheet1" and remains closed at location "C:\dncs\dnc.xlsx".
Each week I am given a different spreadsheet with a single page named "Report 1", called "checkme.xlsx", which I must compare to the reference spreadsheet called dnc.xlsx .
The first two columns in checkme.xlsx are person and DOB, along with data in columns C through F which are irrelevant.
If I find the same DOB in checkme.xlsx as exists in ANY ROW of Sheet1 of dnc.xlsx then I must type into checkme.xlsx on the matching DOB row the text value "DNC" into Column G of Report 1 and also type in the name (from column A of Sheet1 of dnc.xlsx, same row as the DOB) into Column H of Report 1 of checkme.sls.
IN THE ATTACHED EXAMPLE FILE OF CHECKME.xlsx I HAVE DONE THIS FOR THE TWO ROWS THAT HAVE MATCHING DOBS. I just want this to happen by formulae instead of me laboring over it each week.
What I need to figure out is the syntax for vlookup to compare the value in column B (the DOB) in each row of checkme.xlsx against the entire array of DOB values from the reference spreadsheet called dnc.xlsx and return the text "DNC" into Column G on the appropriate row of checkme.xlsx if the two DOB values match.
If G populates with DNC (no quotes in the cell) then the formula for column H of Report 1 needs to fetch the contents of Column A(name) from the reference spreadsheet dnc.xlsx.
My hope is that I can do a global paste of the formulae for both "G" and "H" each time I get a new checkme.xlsx and have those two columns populate accurately and automagically. The page names and the files names/locations never change, and I won't be editing the reference file while accessing the checkme.xlsxx file.
For column G, I've tried defing an array lookup like this for Column G in the checkme.xlsx file: =IF(ISERROR(VLOOKUP(A1,'C:\dncs\[dnc.xlsx]Sheet1'!$A$1:$A$200,1,FALSE)),"","DNC")
For H, I've tried something like this for column H in the checkme.xlsx file: =IF(G1="DNC",VLOOKUP(A1,'C:\dncs\[dnc.xlsx]Sheet1'!$A$1:$A$200,1,0),"")
There are NOT 200 records in the real dnc.xlsx, but it may over time increase to close to that - it currently has 156 rows of data, I don't know if that matters.
These two formulae do not work as described above.
What do I have wrong or maybe, do I have anything remotely right?
Bookmarks