vlookup not looking up!

[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!

 

[Roger Govier]

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

 

[Peo Sjoblom]

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

 

[Max]

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,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,2,0)
[instead of : =VLOOKUP(E1,C,2,0) ]

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