Hi

I hope someone can help me, please bear with me:

I am trying to do a vlookup and the result is coming back #N/A.

The exact fields match in both worksheets but for some reaon it is not
picking it up? The lookup values are both numeric and alpha numeric.

I thought I would use =value(a2) function. This works but only for the
numeric values otherwise it comes back #VALUE?

EG:

Cell A2 Function Result

30000117 =value(a2) 30000117 (the lookup will now work from this
value)
AAA-AWT5/1 =value(a2) #VALUE!

I've also tried using the =TEXT(a2,0) function. This gives me a value back
for both Numeric and Alpha numeric, but it does not resolve the lookup
problem.

Please please help

If you need any more info let me know

Where you have text values you need to check for any leading or
trailing spaces - try the TRIM( ) function on these in the same way as
VALUE( ) on the numerics.

Hope this helps.

Pete

missymissy wrote:
Hi

I hope someone can help me, please bear with me:

I am trying to do a vlookup and the result is coming back #N/A.

The exact fields match in both worksheets but for some reaon it is not
picking it up? The lookup values are both numeric and alpha numeric.

I thought I would use =value(a2) function. This works but only for the
numeric values otherwise it comes back #VALUE?

EG:

Cell A2 Function Result

30000117 =value(a2) 30000117 (the lookup will now work from this
value)
AAA-AWT5/1 =value(a2) #VALUE!

I've also tried using the =TEXT(a2,0) function. This gives me a value back
for both Numeric and Alpha numeric, but it does not resolve the lookup
problem.

Please please help

If you need any more info let me know

i tried a vlookup on those values 30000117 and AAA-AWT5/1 (both how as
general format) and it works fine.Make sure you have the correct range
selected for the v lookup,ie the lookup values and the columns you want to
return.The other variable is you must have the values sorted or use the false
argument
=VLOOKUP(C7,F8:G9,2,FALSE)
--
paul

remove nospam for email addy!



"missymissy" wrote:

Hi

I hope someone can help me, please bear with me:

I am trying to do a vlookup and the result is coming back #N/A.

The exact fields match in both worksheets but for some reaon it is not
picking it up? The lookup values are both numeric and alpha numeric.

I thought I would use =value(a2) function. This works but only for the
numeric values otherwise it comes back #VALUE?

EG:

Cell A2 Function Result

30000117 =value(a2) 30000117 (the lookup will now work from this
value)
AAA-AWT5/1 =value(a2) #VALUE!

I've also tried using the =TEXT(a2,0) function. This gives me a value back
for both Numeric and Alpha numeric, but it does not resolve the lookup
problem.

Please please help

If you need any more info let me know

Pete

You're a star and I'm very silly.

Can't believe I didn't think of it!



"Pete_UK" wrote:

Where you have text values you need to check for any leading or
trailing spaces - try the TRIM( ) function on these in the same way as
VALUE( ) on the numerics.

Hope this helps.

Pete

missymissy wrote:
Hi

I hope someone can help me, please bear with me:

I am trying to do a vlookup and the result is coming back #N/A.

The exact fields match in both worksheets but for some reaon it is not
picking it up? The lookup values are both numeric and alpha numeric.

I thought I would use =value(a2) function. This works but only for the
numeric values otherwise it comes back #VALUE?

EG:

Cell A2 Function Result

30000117 =value(a2) 30000117 (the lookup will now work from this
value)
AAA-AWT5/1 =value(a2) #VALUE!

I've also tried using the =TEXT(a2,0) function. This gives me a value back
for both Numeric and Alpha numeric, but it does not resolve the lookup
problem.

Please please help

If you need any more info let me know

Thanks for the feedback - glad to help.

Pete

missymissy wrote:
Pete

You're a star and I'm very silly.

Can't believe I didn't think of it!



"Pete_UK" wrote:

Where you have text values you need to check for any leading or
trailing spaces - try the TRIM( ) function on these in the same way as
VALUE( ) on the numerics.

Hope this helps.

Pete

missymissy wrote:
Hi

I hope someone can help me, please bear with me:

I am trying to do a vlookup and the result is coming back #N/A.

The exact fields match in both worksheets but for some reaon it is not
picking it up? The lookup values are both numeric and alpha numeric.

I thought I would use =value(a2) function. This works but only for the
numeric values otherwise it comes back #VALUE?

EG:

Cell A2 Function Result

30000117 =value(a2) 30000117 (the lookup will now work from this
value)
AAA-AWT5/1 =value(a2) #VALUE!

I've also tried using the =TEXT(a2,0) function. This gives me a value back
for both Numeric and Alpha numeric, but it does not resolve the lookup
problem.

Please please help

If you need any more info let me know