• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

why vlookup is not working here!

S

shankar_iyer83

Member
Hi,


I am uploading the link for 2 files..data.xls and citycodes.xls..


I am using Vlookup.in data file linked to city codes file..as shown in file..the output should be "quilon" ..in first cell and so on...BUT ITS SHOWING N/A..I am sure..my vlookup code is correct..whats problem? please help


http://speedy.sh/qRfjX/data.xls

http://speedy.sh/8Gmge/city-codes.xls
 
Hi, shankar_iyer83!

First of all, in workbook data.xls, column B BranchID, you have a leading space.

I modified the VLOOKUP replacing B2 by TRIM(B2) and it didn't work. Just go on checking, if something arises I'll advise.

Regards!
 
Hi, shankar_iyer83!

I've been having troubles with TRIM (but internal ones, because I didn't know it worked differenty than in VBA), so I'm considering to declare it "function non grata" for the rest of february at least :)

If you type in D2 =TRIM(B2) and in E2 =LEN(D2), you'll get a 5 instead of a 4. Please don't ask me why, I don't know, but that's the reason why it didn't work, I guess.

Regards!
 
Hi sirjb7,


after doing that,the answer in first cell..its showing is "0147" but it should show "quilon"..as corrosponding to 0071=quilon is there.. I am pretty sure..code is correct..can u point out mistake..?
 
Hi, shankar_iyer83!

Here it is: B2 cell displays the value unquoted " 0071", but the leading character is not a space (char 32) but a (char 160), that's why TRIM couldn't handle it.

Try this:

=SUSTITUIR(B2;IZQUIERDA(B2;1);"") -----> in english: =SUBSTITUTE(B2,LEFT(B2,1),"")

Regards!

PS: I don't get a 147 value, but indeed Quilon (using RIGHT)
 
Hi, shankar_iyer83!

I found this:

=TRIM(SUBSTITUTE(cell reference,CHAR(160),CHAR(32)))

It's the same method I wrote, and the source is:

http://www.excelforum.com/excel-programming/520859-getting-rid-of-char-160-a.html

Just in case for further reading.

Regards!
 
Hi, shankar_iyer83!

What Excel version are you using? It works from 2007 in advance.

For 2003 you can try:

=REEMPLAZAR(B2;1;1;"") -----> in english: =REPLACE(B2,1,1,"")

Regards
 
HI SirJB7,


Thanks a lot, it worked with right()...I have some queries-why does same formula does not work if I use left(B2, 4) in vlook formulae?


and how u got to know leading character is not a space (char 32) but a (char 160)..I am just into some excel(pivot table, some advacne commands) ..so sorry if it sounds silly! ,,,thanks a lot for help
 
SUBSTITUTE is a valid 2003 function. Did you replace "cell reference" with an actual reference, e.g.

=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))
 
To determine the code, you can do:

=CODE(LEFT(B2,1)


The reason it didn't work, is that the left function was picking up the " " character (since it's at the beginning), and screwing things up.
 
Hi, shankar_iyer83!

Left(b2,4) wouldn't work, it retrieves " 007" instead of "0071" that is retrieved with Right(b2,4). And I used the formula typed by Luke M to find out the ascii value for each char.

And remember that nothing's silly, it's just that each one of us ignore different things than others (Albert Einstein said "Todos somos muy ignorantes, lo que sucede es que no todos ignoramos las mismas cosas", I guess in english it might be "We are all very ignorant, what happens is that not all ignore the same things").

Welcome back any time you need.

Regards!
 
You must log in or register to reply here.
Back
Top