why my vlookups not working

[ewong]

i have 2 sheets

sheet1 has 3 fields: old id, id description, new id
sheet2 has 1 field: old id

i want to add new id to sheet2 through a vlookup

however the formula does not seem to be working..

i believe my formula is right though since i tried it with another method.

i added a new field (old id2) in sheet1 between old id and id description.

the formula in old id2 is =LEFT(cell location,3)... so it's essentially returning the same values in old id....

then i change my vlookup... it works when i did that!

i keep on having to do this everytime..

why is this the case??

the data types are the same so the issue is not that.


can anyone help? thanks!

[Blake 7]

can you upload your workbook and i'll take a look?

[djapigo]

Hi ewong,

I'm wildly guessing here, but maybe it's one of these problems...

1. Make sure you are comparing the same things... in other words, if old id in sheet1 are numbers, then old id in sheet2 must be numbers too... or text values looking up text values...

I wonder if your sheet1![old id] is a number and your sheet2![old id] is text... so when you changed the old id with your formula (which converted it now to a text) then you were able to compare text to text...

2. Maybe your column number is wrong... make sure it is 3, and not 2 or 4...

3. Maybe your table_range is wrong? make sure the first column of the table_range matches the index that you are looking for...

4. Lastly, make sure you have 0 or false (exact match), instead of 1 or true (approx match), as your last variable