Excel VLOOKUP problem

Hello ,

can someone see  why the VLOOKUP formula in B2 does not work

It retreives the value of one line before the good one!! very very strange

In B2 for example it should retreive 27 and it retreives 26.

I have looked at everything without success

Sorry that it is in French

Thank you very very much
articles.xlsx
gadsadAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
There are two problems.

1. "Feuil1" column A has a space at the end of each number.
2. The VLOOKUP should have a ,FALSE or ,0 at the end.

I would suggest changing B2 to

=VLOOKUP(A2 & " ",Feuil1!A1:B7068,2,0)
0
 
Wilder1626Connect With a Mentor Commented:
Here you go with TRIM values in sheet1, and also updated the VLookup
=VLOOKUP(A2,Feuil1!A:B,2,0)

Open in new window


I'm alwas fuzzy about empty spaces. never like them. always good to remove when you work with huge data file or data base.
articles-1.xlsx
0
 
gadsadAuthor Commented:
Thank you so much for finding an empty space. I had never though about that

I tried to do =TRIM(A1) but instead of giving the correct value (000027 without trailing space) it just write in the worksheet =TRIM(A1). As if the function is not calculated

Strange again
Any idea ?

Thank you again
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Wilder1626Commented:
It would be hard to tell. I did the trim on my end and it worked on my post: ID: 40584323

If it all worked, please make sure to accept the answer.

Thanks
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If the formula does not calculate the result, the cell is most likely set to Text. Change the cell format to General, then -- this is important -- edit the cell. and hit enter again. Now the calculated result should show and you can copy the formula down.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Have you seen my answer?
0
 
gadsadAuthor Commented:
I used TRIM to remove last space and VLOOKUP with a FALSE

And it worked fine

Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.