Vlookup error?
 

ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will
return a value of 12 and then i would link this cell to the vlookup fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a #N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG

Vlookup error?
 

The LEFT function returns a TEXT value.

A1 = 12345 (a numeric value)

=LEFT(A1,2) returns the TEXT value 12. That is not the same as the numeric
value 12. VLOOKUP doesn't evaluate TEXT numbers and numeric numbers as being
equal so you get #N/A.

Add 2 dashes in front of the LEFT function like this:

=VLOOKUP(INDEX(--LEFT(reference!G3:G7,2),reference!G9),Brackets!A2: F5,6)

The dashes, known as a double unary minus, will coerce the TEXT number into
a numeric number.

--
Biff
Microsoft Excel MVP


"TG" wrote in message
...
ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a
12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function
works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a
value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will
return a value of 12 and then i would link this cell to the vlookup fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a #N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG

Vlookup error?
 

I am assuming that 12 is a number and not text...

LEFT(reference!G3:G7,2) returns a string not a number. This may be the
source of your problem.

Put =ISNUMBER(INDEX(LEFT(reference!G3:G7,2),reference! G9)) in a cell and check

To convert to number you can multiply by 1 and then pass to VLOOKUP.
"TG" wrote:

ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will
return a value of 12 and then i would link this cell to the vlookup fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a #N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG

Vlookup error?
 

Hi,

Try

=VLOOKUP(VALUE(INDEX(LEFT(reference!G3:G7,2),refer ence!G9)),Brackets!A2:F5,6)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"TG" wrote in message
...
ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a
12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function
works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a
value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will
return a value of 12 and then i would link this cell to the vlookup fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a #N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG

Vlookup error?
 

Thank you very much, this works really well!
you guys are great!

"Sheeloo" wrote:

I am assuming that 12 is a number and not text...

LEFT(reference!G3:G7,2) returns a string not a number. This may be the
source of your problem.

Put =ISNUMBER(INDEX(LEFT(reference!G3:G7,2),reference! G9)) in a cell and check

To convert to number you can multiply by 1 and then pass to VLOOKUP.
"TG" wrote:

ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a 12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will
return a value of 12 and then i would link this cell to the vlookup fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a #N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG

Vlookup error?
 

Thank you very much, now I know of 2 ways to go about this problem.
Am really glad microsoft has not yet made this resource "pay per answer"!

"T. Valko" wrote:

The LEFT function returns a TEXT value.

A1 = 12345 (a numeric value)

=LEFT(A1,2) returns the TEXT value 12. That is not the same as the numeric
value 12. VLOOKUP doesn't evaluate TEXT numbers and numeric numbers as being
equal so you get #N/A.

Add 2 dashes in front of the LEFT function like this:

=VLOOKUP(INDEX(--LEFT(reference!G3:G7,2),reference!G9),Brackets!A2: F5,6)

The dashes, known as a double unary minus, will coerce the TEXT number into
a numeric number.

--
Biff
Microsoft Excel MVP


"TG" wrote in message
...
ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9) returns a
12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function
works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a
value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it will
return a value of 12 and then i would link this cell to the vlookup fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a #N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG

Vlookup error?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"TG" wrote in message
...
Thank you very much, now I know of 2 ways to go about this problem.
Am really glad microsoft has not yet made this resource "pay per answer"!

"T. Valko" wrote:

The LEFT function returns a TEXT value.

A1 = 12345 (a numeric value)

=LEFT(A1,2) returns the TEXT value 12. That is not the same as the
numeric
value 12. VLOOKUP doesn't evaluate TEXT numbers and numeric numbers as
being
equal so you get #N/A.

Add 2 dashes in front of the LEFT function like this:

=VLOOKUP(INDEX(--LEFT(reference!G3:G7,2),reference!G9),Brackets!A2: F5,6)

The dashes, known as a double unary minus, will coerce the TEXT number
into
a numeric number.

--
Biff
Microsoft Excel MVP


"TG" wrote in message
...
ok so my problem is when using
=VLOOKUP(INDEX(LEFT(reference!G3:G7,2),reference!G 9),Brackets!A2:F5,6)it
returns a #NA. Where INDEX(LEFT(reference!G3:G7,2),reference!G9)
returns a
12!

If i were to put a number 12 instead of
"INDEX(LEFT(reference!G3:G7,2),reference!G9)" then the vlookup function
works
as intended.

in other words if i were to just type in
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in a cell it will return a
value
of 12, so why does it not work on vlookup.

another thing i have tried is to put
=INDEX(LEFT(reference!G3:G7,2),reference!G9) in lets say Cell A5, it
will
return a value of 12 and then i would link this cell to the vlookup
fomula
like below:
=VLOOKUP(reference!A5,Brackets!A2:F5,6) and even though "reference!A5"
returns a value of 12, the vlookup function does not work it gives a
#N/A.
may this be because Vlookup cannot nest to many references??

Thanks in advance
TG