Vlookup problem

pete4monc

New Member
Joined
Jan 12, 2014
Messages
44
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,807
Office Version
365
Platform
Windows
How about
=VLOOKUP(B5,CustomerService1!B5:J20,9,FALSE)
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
84
Office Version
365
Platform
Windows
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

Board Regular
Joined
Sep 22, 2019
Messages
84
Office Version
365
Platform
Windows
And range is off as spotted by Fluff
 

pete4monc

New Member
Joined
Jan 12, 2014
Messages
44
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

Board Regular
Joined
Sep 22, 2019
Messages
84
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,807
Office Version
365
Platform
Windows
Is it returning 01/01/1900 or 00/01/1900 (in dd/mm/yyyy format)?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,807
Office Version
365
Platform
Windows
Are you trying to return values from column H, I, or J?
Because from that image it looks as though J is blank
 

Watch MrExcel Video

Forum statistics

Threads
1,099,281
Messages
5,467,733
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top