# How do I find data from one spreadsheet in another, and compare values

This is a discussion on How do I find data from one spreadsheet in another, and compare values within the Excel Questions forums, part of the Question Forums category; First, you guys are awesome. You solved my last problem perfectly. Of course, since no good deed goes unpunished, I ...

1. ## How do I find data from one spreadsheet in another, and compare values

First, you guys are awesome. You solved my last problem perfectly. Of course, since no good deed goes unpunished, I have another question.

Spreadsheet 1, "NPI.xls" contains (among other things) the following fields
A1 - NPI (it is a unique ID for a doctor)
B1 - Last Name
C1 - First Name
(There are around 13,000 rows)

A1 - NPI
B1 - Last Name
C1 - First Name
(around 60,000 rows)

What I want to do is go to spreadsheet 2 and select the first NPI, find it in spreadsheet 1. If found, then compare to determine if last name and first names are identical. There is no certainty that the find will work. If the find fails or succeeds, fine, move on to the next row.

Thanks!
Pete

2. ## Re: How do I find data from one spreadsheet in another, and compare values

This formula should do the trick:

Code:
=B2 & C2 =VLOOKUP(A2,Sheet1!\$A\$2:\$C\$500,2,FALSE) & VLOOKUP(A2,Sheet1!\$A\$2:\$C\$500,3,FALSE)
Will compare the first and last names and return true or false.
or
Does B2 & C2 equal column B & C on whichever row the the NPI number appears between rows 2 & 500.

Edit: Will return an #N/A error if the NPI doesn't appear on both sheets.

3. ## Re: How do I find data from one spreadsheet in another, and compare values

Boy, I've spent an hour trying to make this work.

I am sure that I am completely wrong, but it looks like the formula is self-referring. In my example, I have two separate spreadsheets, NPI, and PECOS. I am doing the work in PECOS, using NPI as having the NPI value and Last/first that I want to compare.

When I implement your solution, I pasted it into column D2 of PECOS. The way that it seems to read to me, it is looking into the PECOS spreadsheet, not the NPI spreadsheet.

Guessing that perhaps you were expecting me to copy the NPI spreadsheet into Sheet1 of the PECOS spreadsheet, (since your formula refers to Sheet1 multiple times) I gave that a try. Now when I try, I only get a result of #NA.

Clearly we are on to something, but I just don't have the experience to kick it to the next level.

Any help would be appreciated!

Thanks,
Pete

4. ## Re: How do I find data from one spreadsheet in another, and compare values

You are correct "NPI.xls" goes on Sheet 1 and "PECOS.xls" sheet 2. The formula goes to "PECOS" D2 then copied down. #N/A appears when the NPI cannot be found and or the surname & given name differ. If the NPI & surname & given name are the same then TRUE returns

If all you get is #N/A I suggest you check for spaces or some other anomaly in the ID, surname and given name fields.

you say there are 60,000 odd lines to check, you'll need to amend the \$C\$500 to the total line items

5. ## Re: How do I find data from one spreadsheet in another, and compare values

Outstanding sir! I'll be augmenting this extensively to include comparing addresses and the like, so this is very very helpful, thanks.

Ok, now a slightly different take. I am finding that popping back and forth between the sheets to be time consuming. Is there a way to use the same formula to 'drag over' the LName and FName to Sheet2, into columns e and f?

This way if Sheet 2 is SMITH, BILL
And Sheet 1 is SM1TH, BILL (note the "1" instead of the "I")

Then on Sheet 2, we'll have

A2 = SMITH
B2 = BILL
D2 =
E2 = SM1TH
F2= BILL

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•