VLOOKUP and "-" characters

Hi,

I'm using VLOOKUP to to convert a set of cells with values based on a lookup
table. Some of these defined values are "C--" and "C-" as per the example
below. Does anyone know why the formula does not give the correct answer?

A B
1 C-- 1
2 C- 2
3 C 3
4 C+ 4
5 C++ 5
6
7
8 C++ 5
9 C+ 4
10 C 3
11 C- 3
12 C-- 3

(B8 fomula =VLOOKUP(A8,$A$1:$B$5,2) delivers correct value)
(B9 fomula =VLOOKUP(A9,$A$1:$B$5,2) delivers correct value)
(B10 fomula =VLOOKUP(A10,$A$1:$B$5,2) delivers correct value)
(B11 fomula =VLOOKUP(A11,$A$1:$B$5,2) delivers incorrect value, should be 2)
(B12 fomula =VLOOKUP(A12,$A$1:$B$5,2) delivers incorrect value, should be 1)

I have tried changing the order of column A array, changing the cell format
of column A to text/general but with the same result. If I replace C-- and C-
with something else then it works. Have tried it with both XP/Excel 2003 and
XP/Excel 2007

Any suggestions please?
/Exxet

=VLOOKUP(A8,$A$1:$B$5,2)

You need to use the 4th argument to VLOOKUP:

=VLOOKUP(A8,$A$1:$B$5,2,0)

The 4th argument set as 0 or FALSE tells Excel to look for an exact match.

--
Biff
Microsoft Excel MVP


"Exxet" wrote in message
...
Hi,

I'm using VLOOKUP to to convert a set of cells with values based on a
lookup
table. Some of these defined values are "C--" and "C-" as per the example
below. Does anyone know why the formula does not give the correct answer?

A B
1 C-- 1
2 C- 2
3 C 3
4 C+ 4
5 C++ 5
6
7
8 C++ 5
9 C+ 4
10 C 3
11 C- 3
12 C-- 3

(B8 fomula =VLOOKUP(A8,$A$1:$B$5,2) delivers correct value)
(B9 fomula =VLOOKUP(A9,$A$1:$B$5,2) delivers correct value)
(B10 fomula =VLOOKUP(A10,$A$1:$B$5,2) delivers correct value)
(B11 fomula =VLOOKUP(A11,$A$1:$B$5,2) delivers incorrect value, should be
2)
(B12 fomula =VLOOKUP(A12,$A$1:$B$5,2) delivers incorrect value, should be
1)

I have tried changing the order of column A array, changing the cell
format
of column A to text/general but with the same result. If I replace C-- and
C-
with something else then it works. Have tried it with both XP/Excel 2003
and
XP/Excel 2007

Any suggestions please?
/Exxet

Add
,FALSE
just before the last parentheses in all your formulas
Without it, it's assuming the data is in ascending sequence; with it, it's
looking for an exact match

"Exxet" wrote:

Hi,

I'm using VLOOKUP to to convert a set of cells with values based on a lookup
table. Some of these defined values are "C--" and "C-" as per the example
below. Does anyone know why the formula does not give the correct answer?

A B
1 C-- 1
2 C- 2
3 C 3
4 C+ 4
5 C++ 5
6
7
8 C++ 5
9 C+ 4
10 C 3
11 C- 3
12 C-- 3

(B8 fomula =VLOOKUP(A8,$A$1:$B$5,2) delivers correct value)
(B9 fomula =VLOOKUP(A9,$A$1:$B$5,2) delivers correct value)
(B10 fomula =VLOOKUP(A10,$A$1:$B$5,2) delivers correct value)
(B11 fomula =VLOOKUP(A11,$A$1:$B$5,2) delivers incorrect value, should be 2)
(B12 fomula =VLOOKUP(A12,$A$1:$B$5,2) delivers incorrect value, should be 1)

I have tried changing the order of column A array, changing the cell format
of column A to text/general but with the same result. If I replace C-- and C-
with something else then it works. Have tried it with both XP/Excel 2003 and
XP/Excel 2007

Any suggestions please?
/Exxet

Add a 0 or false as the final argument to your VLOOKUP to force it to find an
exact match

=VLOOKUP(A12,$A$1:$B$5,2,0)

"Exxet" wrote:

Hi,

I'm using VLOOKUP to to convert a set of cells with values based on a lookup
table. Some of these defined values are "C--" and "C-" as per the example
below. Does anyone know why the formula does not give the correct answer?

A B
1 C-- 1
2 C- 2
3 C 3
4 C+ 4
5 C++ 5
6
7
8 C++ 5
9 C+ 4
10 C 3
11 C- 3
12 C-- 3

(B8 fomula =VLOOKUP(A8,$A$1:$B$5,2) delivers correct value)
(B9 fomula =VLOOKUP(A9,$A$1:$B$5,2) delivers correct value)
(B10 fomula =VLOOKUP(A10,$A$1:$B$5,2) delivers correct value)
(B11 fomula =VLOOKUP(A11,$A$1:$B$5,2) delivers incorrect value, should be 2)
(B12 fomula =VLOOKUP(A12,$A$1:$B$5,2) delivers incorrect value, should be 1)

I have tried changing the order of column A array, changing the cell format
of column A to text/general but with the same result. If I replace C-- and C-
with something else then it works. Have tried it with both XP/Excel 2003 and
XP/Excel 2007

Any suggestions please?
/Exxet

Many thanks to all three of you. You solved my problem. Thanks.

/Exxet

"Duke Carey" wrote:

Add a 0 or false as the final argument to your VLOOKUP to force it to find an
exact match

=VLOOKUP(A12,$A$1:$B$5,2,0)

"Exxet" wrote:

Hi,

I'm using VLOOKUP to to convert a set of cells with values based on a lookup
table. Some of these defined values are "C--" and "C-" as per the example
below. Does anyone know why the formula does not give the correct answer?

A B
1 C-- 1
2 C- 2
3 C 3
4 C+ 4
5 C++ 5
6
7
8 C++ 5
9 C+ 4
10 C 3
11 C- 3
12 C-- 3

(B8 fomula =VLOOKUP(A8,$A$1:$B$5,2) delivers correct value)
(B9 fomula =VLOOKUP(A9,$A$1:$B$5,2) delivers correct value)
(B10 fomula =VLOOKUP(A10,$A$1:$B$5,2) delivers correct value)
(B11 fomula =VLOOKUP(A11,$A$1:$B$5,2) delivers incorrect value, should be 2)
(B12 fomula =VLOOKUP(A12,$A$1:$B$5,2) delivers incorrect value, should be 1)

I have tried changing the order of column A array, changing the cell format
of column A to text/general but with the same result. If I replace C-- and C-
with something else then it works. Have tried it with both XP/Excel 2003 and
XP/Excel 2007

Any suggestions please?
/Exxet