Vlookups In Excel

[andyGTBristol]

Hi,

I have two seperate spreadsheets of data, is possible to run a VLOOKUP from one against the other ?

I need to verify an account number and part of a name.

Thanks

[SeaJayPea]

The simple answer is yes. Could do with a bit more information before suggesting how you might do it, though.

[andyGTBristol]

Thanks, I have actually copied the smaller spreadsheet on to a second sheet of the larger first one as I thought that might be easier to work with. Sheet 1 has some 7000 accounts on and sheet 2, has approx 200, I need be able look up sheet 2 against sheet 1 and see if any are missing

[dr b]

Just use:

=VLOOKUP(A1,[Book2]Sheet1!$A$1:$B$5,1,FALSE)

where [Book2] and Sheet1 are the other workbook.

[dr b]

Well, now it would be:

=VLOOKUP(A1,Sheet2!$A$1:$B$5,1,FALSE)

where Sheet2 is the other sheet in this workbook

[andyGTBristol]

Thanks Dr b, I tried your second formula as both sheets are now in the same workbook but I keep getting an #N/A error. In sheet 1 the account numbers begin cell A2 (A1 being the header) and in sheet 2 they begin in D2 (D1 as the header)

[dr b]

You're just trying to confirm that an account # in Sheet1 A is also in Sheet 2 D?

So in Sheet 1 cell B2 you should have:

=VLOOKUP(A2,Sheet2!$D$2:$D$1000,1,FALSE)

if you get #N/A that means that the account number in A2 does not appear anywhere in Sheet2 column D.

[andyGTBristol]

Yep that's right and I can just drag the formula down to check each line of column D against A on sheet 1 ?

[Calibax]

Are the account numbers in both sheets the same format (i.e. both text or both numeric and the same length)?

[dr b]

yes, because you're using absolute cell references ($D$2:$D$1000) those will not change as you drag b2 down the column.

[andyGTBristol]

They're all text, but they are various lengths

[dr b]

As long as the same account number is in the same format in both places, the VLOOKUP will find it. But if the same account number has an extra blank in it or something like that you'll not get a match, ie VLOOKUP will match:

12 34 with 12 34

but not

1 234 with 123 4