vlookup problem

M

mkk

I am trying to a vlookup on formatted text. I I had a long string and I
extracted certain characters from it using the "mid" function. Now if I
do a vlookup on the extracted list it doesnt work. But if I were to
actually type in the values in the extracted list the vlookup starts
working. I tried changing the format of the extracted list to general,
number and text..nothing works.

Can anybody please tell me what I am doing wrong? I know my vlookup is
correct because it works when i type the values in the table array.

thanks
 
F

Frank Kabel

Hi
you may have spaces or other characters in your extracted string. Try
using TRIM on your extracted string and compare this result manually
with your lookup range. e.g.
TRIM(MID(...)) = cell_reference_in_your_list
Should return TRUE. Otherwise there are still some invisible characters

Frank
 
M

mkk

Tried that....trim doesnt change anything....how do i delete th
invisible characters?

Thank
 
F

Frank Kabel

Tried that....trim doesnt change anything....how do i delete the
invisible characters?

Thanks
Click to expand...

Hi
try the formula
=CODE(LEFT(A1,1))
to get the code for that char at beginning. Repeat this for all
characters in your string. Probably a CHR(160) included.
Frank
 
N

Niek Otten

One way to find out if there's any hidden character is by applying the LEN()
function.
But personally, I suspect something else is the case. No clue yet, however.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
M

mkk

thanks for all the posts. I found the solution, I tried formatting al
the cells just by going fomat->text. That didnt work so I used the tex
function to make them all text. It works now. I guess it was just
question of type inconsistency.

Thanks,
M
 
Top