VLookup

I have a spreadsheet with two worksheets.

Sheet 1 contains the following columns and data.

Column A
XYZ000009
XYZ000010
XYZ000011
XYZ000012

Column B
Bloggs & Co
Smith Ltd
Acme & Co
Cooper Ltd

Sheet 2 contains the following:

Column A
XYZ00001
XYZ00009
XYZ000010
XYZ000011
XYZ000012

Column B
NO ID
#REF!
#REF!
#REF!
#REF!

I basically want column B to be populated with the details from Sheet1, Column B if A = A and if not found populate with the text "NO ID". However for valid results, I'm getting #REF!

The formula I'm using is:

=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,Sheet1!B:B,FALSE)),"No APW ID",VLOOKUP(A1,Sheet1!A:A,Sheet1!B:B,FALSE))

I'm assuming this is because the value of Column A is text and not a numerical value but am having a real blonde moment as to how I can solve this.

Thanks in advance for any help with this

=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,Sheet1!B:B,FALSE)),"No APW ID",VLOOKUP(A1,Sheet1!A:A,Sheet1!B:B,FALSE))

try

Please Login or Register  to view this content.