CODEWITHSUNDEEP
excelexcel-2007excel-formulavlookup
Jacob
Jacob

Reputation: 829

Incorrect using vlookup formula

I have in excel one table:

AAl 1
AAr 2
Abu 3

and the second

AAl
AAr
ABa
ABe
ABu

I would like have the same numbers in second table as in the first table. I am using vlookup formula:

=VLOOKUP(BL3;$BI$3:$BJ$5;2;0)

BL3 is the first row in second table and $BI$3:$BJ$5 is the first table. Unfortunately, I received:

AAl 1
AAr 2
ABa #N/A
ABe #N/A
ABu #N/A

The last row is incorrect because ABu should have number 3. Could you help me?

Upvotes: 0

Views: 472

Answers (2)

PKumar
PKumar

Reputation: 11128

I think you should use trim, I got it using trim =+VLOOKUP(TRIM(C5),$A$1:$B$3,2,0)

EDIT:

I have added the screenshot which clearly shows an extra space in notepad++,

enter image description here

Upvotes: 1

Jerry
Jerry

Reputation: 71538

Make sure that there are no extra spaces in your data. From your question, there seems to be spaces, so you might try:

=VLOOKUP(TRIM(BL3);$BI$3:$BJ$5;2;0)

hoping that the extra space characters aren't some sort of other character you can't easily see. Otherwise, remove the extra spaces manually, and you should be able to capture most invisible characters if you do a =LEN() on the cell and see what the character count is not correct.

Upvotes: 3

Related Questions