Hi, I've been trying to find a solution to this all day. Any help to get me
past this would be much appreciated.

Using VLOOKUP, I have a car license plate e.g. AB51 DVL.
On a separate worksheet I have a table with the CODE (51 etc) in one column
and the DATE (e.g. Sept 2001) in the next. I need to create a VLOOKUP so that
when a licence plate is entered, the date that corresponds to the code in
that licence plate is displayed in the correct cell. When I type in the "51"
in the VLOOKUP I get the correct answer, but when I type the cell (e.g.A3)
where the licence plate is I get #N/A. I have tried to use MID function, for
example =MID(A3,3,2) which extracts the number from the licence plate into
another cell and have used that cell as reference, but I still get the #N/A.
Any help would be much appreciated. Thanks in advance.
--
Business Computing Student

Hi,

Mid is extracting 51 as a text string and your comparing it to a number on
your oyher sheet. Add 0 to the lookup value to turn that into a number and
all should be fine

=VLOOKUP(MID(A3,3,2)+0,Sheet2!A1:B11,2,FALSE)

Mike

"twins160" wrote:

Hi, I've been trying to find a solution to this all day. Any help to get me
past this would be much appreciated.

Using VLOOKUP, I have a car license plate e.g. AB51 DVL.
On a separate worksheet I have a table with the CODE (51 etc) in one column
and the DATE (e.g. Sept 2001) in the next. I need to create a VLOOKUP so that
when a licence plate is entered, the date that corresponds to the code in
that licence plate is displayed in the correct cell. When I type in the "51"
in the VLOOKUP I get the correct answer, but when I type the cell (e.g.A3)
where the licence plate is I get #N/A. I have tried to use MID function, for
example =MID(A3,3,2) which extracts the number from the licence plate into
another cell and have used that cell as reference, but I still get the #N/A.
Any help would be much appreciated. Thanks in advance.
--
Business Computing Student

Hi,
your problem is probably due to the fact that in your code column you
have numbers, whilst the MID function will give a string
Reapplay your vlookup as follows =vlookup(mid(a3,3,2)*1,range,colnr)
This should help you through

HTH

twins160;326623 Wrote:
Hi, I've been trying to find a solution to this all day. Any help to get
me
past this would be much appreciated.

Using VLOOKUP, I have a car license plate e.g. AB51 DVL.
On a separate worksheet I have a table with the CODE (51 etc) in one
column
and the DATE (e.g. Sept 2001) in the next. I need to create a VLOOKUP
so that
when a licence plate is entered, the date that corresponds to the code
in
that licence plate is displayed in the correct cell. When I type in the
"51"
in the VLOOKUP I get the correct answer, but when I type the cell
(e.g.A3)
where the licence plate is I get #N/A. I have tried to use MID
function, for
example =MID(A3,3,2) which extracts the number from the licence plate
into
another cell and have used that cell as reference, but I still get the
#N/A.
Any help would be much appreciated. Thanks in advance.
--
Business Computing Student


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91260

I have this starting in A1 of Sheet2
code date
45 Mar-01
46 Apr-01
47 May-01
48 Jun-01
49 Jul-01
50 Aug-01
51 Sep-01
52 Oct-01
53 Nov-01


In A1 of Sheet 1 I have AB51 DVL
In B1 I have =VLOOKUP(--MID(A1,3,2),Sheet2!A2:B10,2,FALSE)
Not the double negation to convert the teat value 51 into a numeric value
The result is as expected when I format B1 to show dates (on sheet2 the
dates is actually 1-Mar-2001, 1-Apr-2001, etc)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"twins160" wrote in message
...
Hi, I've been trying to find a solution to this all day. Any help to get
me
past this would be much appreciated.

Using VLOOKUP, I have a car license plate e.g. AB51 DVL.
On a separate worksheet I have a table with the CODE (51 etc) in one
column
and the DATE (e.g. Sept 2001) in the next. I need to create a VLOOKUP so
that
when a licence plate is entered, the date that corresponds to the code in
that licence plate is displayed in the correct cell. When I type in the
"51"
in the VLOOKUP I get the correct answer, but when I type the cell (e.g.A3)
where the licence plate is I get #N/A. I have tried to use MID function,
for
example =MID(A3,3,2) which extracts the number from the licence plate into
another cell and have used that cell as reference, but I still get the
#N/A.
Any help would be much appreciated. Thanks in advance.
--
Business Computing Student

If you type 51 into a cell, then unless you have preformatted the cell as
text the 51 will be stored as a number. If you extract 51 with the MID
function it will be text. The data range in which you are doing the lookup
is presumably stored as numbers, so the text won't be found.
If you want to convert the text to numbers, change =MID(A3,3,2) to
=--MID(A3,3,2)
--
David Biddulph

"twins160" wrote in message
...
Hi, I've been trying to find a solution to this all day. Any help to get
me
past this would be much appreciated.

Using VLOOKUP, I have a car license plate e.g. AB51 DVL.
On a separate worksheet I have a table with the CODE (51 etc) in one
column
and the DATE (e.g. Sept 2001) in the next. I need to create a VLOOKUP so
that
when a licence plate is entered, the date that corresponds to the code in
that licence plate is displayed in the correct cell. When I type in the
"51"
in the VLOOKUP I get the correct answer, but when I type the cell (e.g.A3)
where the licence plate is I get #N/A. I have tried to use MID function,
for
example =MID(A3,3,2) which extracts the number from the licence plate into
another cell and have used that cell as reference, but I still get the
#N/A.
Any help would be much appreciated. Thanks in advance.
--
Business Computing Student

Thank you all ever so much, I knew I was missing something out. It works
perfectly now with the =--MID(A3,3,2) .

Business Computing Student

Glad it helped.
--
David Biddulph

"twins160" wrote in message
...
Thank you all ever so much, I knew I was missing something out. It works
perfectly now with the =--MID(A3,3,2) .

Business Computing Student