vlookup not looking up!

  • Thread starter Thread starter hobo jojo
  • Start date Start date
H

hobo jojo

This is probably something very simple but I can't get vlookup to
actually lookup....

i'l explain more clearly.

I have a bunch of staff codes on a goals form, these come in the format
of ME00 ###013.

i am trying to get vlookup to bring up when these staff members are
rota'd to work but on the rota file they are listed simply by the three
digit code.

I have managed to sort it so that I have just the three digits by using
MID but vlookup will not look this three digit number up! it will only
look it up if i actually type in the code which is no good as i need
this to be as automated as possible.

any help apreciated!
 
Hi

It sounds as though you need to convert your extracted value from MID into
numeric to compare with the vlookup table.

=--MID(A1,9,3)
or
=--(RIGHT(A1,3)

Regards

Roger Govier
 
MID returs a text string while the codes in the table are probably numbers,
thus the mismatch, corce MID to retur a number

=--MID(xxxx)

will return a numeric value (if the xtracted characters are numbers) and
unless you have invisible characters in the table it should work
 
2 guesses ..

In your col with MID(...) extracting the last 3 digits (which is assumed to
be the lookup col), try adding a zero*, for example: =MID(A1,B1+3,99)+0
Then copy down the column
*one way to coerce the text numbers to real numbers

Now the vlookup, eg: =VLOOKUP(E1,C:D,2,0)
should work if col E contains real numbers

Or, the other way round:
leave the text numbers in the MID col alone,
and try something like: =VLOOKUP(TEXT(E1,"000"),C:D,2,0)
[instead of : =VLOOKUP(E1,C:D,2,0) ]

The TEXT(...) will convert the real numbers in col E
to text numbers for matching
 
Back
Top