Vlookup problem

[pete4monc]

Hi gents
I am trying to lookup the order number in one spreadsheet (Input Sheet, column B) matching it with the same number on another spreadsheet (CustomerService, column B) and then return the date it was finished on the CustomerService sheet, column J. But it keeps coming up with #N/A and if I put in iferror, it returns nothing?
On both sheets the order numbers are in columns B and the date on the customerservice sheet is in column J
=IFERROR(VLOOKUP(B5,CustomerService1!A5:I20,9,FALSE),"")

Thanks in advance.

 

[Fluff]

How about
=VLOOKUP(B5,CustomerService1!B5:J20,9,FALSE)

 

[denzo36]

Hi there

Check to see if there are trailing spaces in both order number columns which will throw the VLOOKUP out.

Also ensure the data types of both order number columns are the same, they may appear as numbers but actually be formatted as text - Ctrl+1 to check the format

 

[denzo36]

And range is off as spotted by Fluff

 

[pete4monc]

lol.....it now returns a date but the date is 01/01/1900? Which is obviously not the date on the customerservice spreadsheet?
I have checked the format is the same on both spreadsheets.
Thanks for your help.

 

[denzo36]

00/01/1900 is what returns in a blank cell if that cell is formatted as a date - are you sure its returning 01/01/1900? Are there blanks in your date column?

Maybe share a screenshot of your spreadsheet?

 

[Fluff]

Is it returning 01/01/1900 or 00/01/1900 (in dd/mm/yyyy format)?

 

[pete4monc]

 

[pete4monc]

And this is the customerservice sheet

 

[Fluff]

Are you trying to return values from column H, I, or J?
Because from that image it looks as though J is blank