[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

Excel VLOOKUP problem

Posted on 2015-02-02
Medium Priority
157 Views
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
0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +1

LVL 24

Accepted Solution

Phillip Burton earned 1000 total points
ID: 40584311
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

LVL 11

Assisted Solution

Wilder1626 earned 1000 total points
ID: 40584323
Here you go with TRIM values in sheet1, and also updated the VLookup
``````=VLOOKUP(A2,Feuil1!A:B,2,0)
``````

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

Author Comment

ID: 40584508
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

LVL 11

Expert Comment

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

Thanks
0

LVL 50

Expert Comment

ID: 40584630
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

LVL 24

Expert Comment

ID: 40584927
0

Author Closing Comment

ID: 40585023
I used TRIM to remove last space and VLOOKUP with a FALSE

And it worked fine

Thank you very much
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
Suggested Courses
Course of the Month12 days, 15 hours left to enroll