Vlookup formula is not picking what I want due to wrong format - but i cant change it

[3smees23]

Hi,

Im trying to turn a manufacturer code into a supplier code using a vlookup formula. I has changed the majority of them but there are some codes that the formula is not picking up.

I have applied a filter to identify the problem items and have highlighted the column that I need the supplier code. The supplier code on sheet 3 is column F.

I know that the text codes at the top of 'sheet 1' have trailing spaces and that they need to be trimmed but i need to some how change the format of the numeric codes below that that are throwing out #N/As.

Please can some one explain what i need to do to the format of these codes.

Thanks for you help

Sam

[JBeaucaire]

It's a common problem. In Sheet3 cell A604 is the code VPD-0-DF-S, but there is also a trailing space, so it's not the same string. You'll need to clean up those space tails. The TRIM() function will do it, the edit/pastespecial-values back over the original data.

[Palmetto]

At first glance this doesn't appear to be stricly a format issue.
If you try a manual search using the built in Find feature (Ctrl+F) the values for which #NA is shown are not found.

Thus VLOOKUP appears to be returning the correct result because the value is, in fact, not in the list.

You do need to normalize your data: numeric values should be numeric. You can quickly do this with a Copy | Paste Special | Multiply (x1)

[3smees23]

Hi,

I knew about the trailing spaces on the codes at the top, thats something i didnt get round to doing before posting.

What I cant understand is the PAC, Faac and Fermax products. As they are present in sheet 3. IE C4218 in sheet 1 is row 5499 in sheet 3. so theyre definatley there. when i click on the little green triangle in sheet 3 and convert it to a number it then appears in sheet 1.

your copy, paste special and x1 thing worked Palmetto. but why cant i just turn them into numeric numbers by right clicking and formating that way.

Please help

[JBeaucaire]

Post removed.