vlook up problems need sort?

For the life of me I can't get this vlookup to work..

I have this file.. VB i called it. I need to do vlookup in column B. If it matches the id in the second file(master deptid) it is supposed to return the second column which is the description. Does it need to sorted or something ? because it keeps coming up with N/A. Also, if the entry doesn't exist.. it should just return blank.

=VLOOKUP(A3,'MASTER DEPT ID 3table.xls'!$C$2:$D$635,2)
That is what I was trying to use. Thanks for any input you guys might have.

For VLOOKUP to work correctly you need to have the same data type for LOOKUP value (A3) and LOOKUP range ('MASTER DEPT ID 3table.xls'!$C$2:$C$635) but A3 is numeric and the range in table.xls is text.

One way to solve this is to change the table.xls range to numeric. Select column C

Data > Text to Columns > Finish

that's it.

As your lookup range is not sorted you need Bryan's suggestion, a 4th argument for VLOOKUP of FALSE or 0.

This will still give you #N/A when the number doesn't exist in the table so to return a blank try this formula copied down

=IF(ISNA(MATCH(A3,'MASTER DEPT ID 3table.xls'!$C$2:$C$635,0)),"",VLOOKUP(A3,'MASTER DEPT ID 3table.xls'!$C$2:$D$635,2,0))

Try this:

=IF(ISERROR(MATCH(""&A3,'MASTER DEPT ID 3table.xls'!$C:$C,0)),"",INDEX('MASTER DEPT ID 3table.xls'!$B:$B,MATCH(""&A3,'MASTER DEPT ID 3table.xls'!$C:$C,0)))

I think there were 2 problems with using VLOOKUP for this (one of which also applies to using MATCH & INDEX for this).

Problem #1: (applies to both MATCH & VLOOKUP) the entries in Column A of "vb" file appear to be numeric, while the entries in Columns A & C of the "MASTER DEPT ID" are text. That is why you found no match, I think.

Problem #2: (applies only to VLOOKUP) with VLOOKUP, the "lookup" column must be the first column, and the "return" column must be to the right of the "lookup" column. Using MATCH, you can return the row number where the match was found, then using INDEX you can return the results from any column you choose.