vlookup problem

  • Thread starter Thread starter mkk
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
Back
Top