vlookup not looking up!
 

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!


--
hobo jojo
------------------------------------------------------------------------
hobo jojo's Profile: http://www.excelforum.com/member.php...o&userid=28459
View this thread: http://www.excelforum.com/showthread...hreadid=480590

vlookup not looking up!
 

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


hobo jojo wrote:
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!

vlookup not looking up!
 

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

--

Regards,

Peo Sjoblom

"hobo jojo" wrote
in message ...

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!


--
hobo jojo
------------------------------------------------------------------------
hobo jojo's Profile:
http://www.excelforum.com/member.php...o&userid=28459
View this thread: http://www.excelforum.com/showthread...hreadid=480590

vlookup not looking up!
 

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"hobo jojo" wrote
in message ...

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!


--
hobo jojo
------------------------------------------------------------------------
hobo jojo's Profile:
http://www.excelforum.com/member.php...o&userid=28459
View this thread: http://www.excelforum.com/showthread...hreadid=480590