Excel 2007 : Vlookup returns #N/A with special Characters.

[eSimon]

Hello,

I have a table with data coming from a DB. I am trying to use voolkup and it returns #N/A.


-uecBgd@Do[XaZ.T5%0g____`&HC#GeTG1S>+s?J#A1g____Name1
+0*2hqH"+sJ\&`X?c[sQ________`&HC#GeTG1S>+s?J#A1g____Name2
9(Dij':``3O7DX@MM________!!!!!!!!!!!!!!!!!!!!_____________Name3
*5bF#XA16=XGfQRXs.&O_______N[0$CF$h<CM0qqoVjoPs____Name4
!!!!!!!!!!!!!!!!!!!!_________________!!!!!!!!!!!!!!!!!!!!_____________Name0
3Y=A[$(c!/VH!ga`,T&u__________9(Dij':``3O7DX@MM___Name8
gQ1rgV*`$kO9JKLjcCC"_________9(Dij':``3O7DX@MM____Name7
N[0$CF$h<CM0qqoVjoPs________9(Dij':``3O7DX@MM____Name78
`&HC#GeTG1S>+s?J#A1g_______9(Dij':``3O7DX@MM____Name10

I tried changing formatting. no success.
Guess all those special characters are confusing Excel.
Is there any hope for me?

Thanks

[oeldere]

You get better help if you post an Excel-example of your workbook, without confidential information.
Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
Use BEFORE/AFTER sheets if that helps make it clearer.

[martindwilson]

that example works fine for me

[eSimon]

This is the case.
In the attached document, in Sheet 1 i have order numbers located in Folder ID.
In Sheet 2, there a list of all Folder ID, Parent Folder ID and Folder Name.
I would like to have in Sheet one, in a new column, beside each Order the name of the Folder.
See sheet1 with examples.

Thanks.

[martindwilson]

its not the characters but the leading and trailing spaces in your data,trouble is cleaning them up starts to produce errors itself
so use this array entered version of vlookup
=VLOOKUP(TRIM(B2),TRIM(Sheet2!$A$2:$B$12),2,0)
see this link on how to enter arrays
http://office.microsoft.com/en-us/ex...872901033.aspx

[eSimon]

Thanks Martin.
I was blinded by all those special characters and i did not see the leading space. I removed the space, but the result from vlookup is the same.
See upadated file.

[martindwilson]

did you not see the formula i posted ? thats because your data has either leading and/or trailing spaces check both sheets sheet 2 has trailing spaces otherwise normally entered =VLOOKUP(TRIM(B2),Sheet2!$A$2:$B$12,2,0) would have worked (note no trim around Sheet2!$A$2:$B$12)

[eSimon]

Thanks,
It worked now.
I TRIMmed the spaces before applyng the formula (just to keep it simpler).

Regards

[Cutter]

Based on your last post I'll mark this thread as SOLVED. Please remember to do that yourself in your future threads. Thanks.