Making Data Formats Match for vlookup

[Colin_S]

I'm trying to do a basic vlookup on three different spreadsheets however i ALWAYS have the issue of the data formats for the reference field not matching. i.e. some are stored as text, some as numbers etc The refernce filds i am using contain the same data but the vlookup can't seem to find them.

What is the easiest way to make them match. Format Cells - Number does not work, i highlight all the and click on the green arrow indicating there is an error with the cells and click convert to number, still doesn't work. Please help.

The reference field is a list of part numbers often begining with a 0. If needs must the 0 could be dropped when formating to a number but would prefer it didn;t have to.

Many Thanks

Colin

[martindwilson]

have a read here
http://www.contextures.com/xlFunctions02.html#Trouble

[Colin_S]

Thanks for your help but the two formulas below do not work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[martindwilson]

well they do! but i dont know how you are using them!
did you try the other options?
=VLOOKUP(--A7,Products!$A$2:$C$5,3,FALSE)
post a sheet with sample data that doesnt work

[Colin_S]

Yes =VLOOKUP(--A7,Products!$A$2:$C$5,3,FALSE) works fine. I have now managed to get the second one i posted to work thanks, but the first one gives me the erroe in the screen dump attacted. That is just a sample sheet.

Many Thanks

Colin

[Cheeky Charlie]

take out the ) after the ""

HTH

[sglife]

It's because the ")"
try this

Please Login or Register  to view this content.