dashes in vlookup
 

I am using vlookup to lookup quantities attached to part numbers. The
formula works fine unless the part number contains a dash (-) then it
will not find the part number in the table array even though they look
identical.
I have tried formatting the cells in various ways but it has made no
difference.
Any ideas??
Thanks,
Tony


--
anthonyn
------------------------------------------------------------------------
anthonyn's Profile: http://www.excelforum.com/member.php...o&userid=16753
View this thread: http://www.excelforum.com/showthread...hreadid=319553

Assuming only the lookup values contain the dashes "-"

Instead of say, in B1 : =VLOOKUP(A1,Sheet2!A:B,2,0)

Try something like:

=VLOOKUP(--SUBSTITUTE(TRIM(A1),"-",""),Sheet2!A:B,2,0)

Copy down

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"anthonyn" wrote in message
...

I am using vlookup to lookup quantities attached to part numbers. The
formula works fine unless the part number contains a dash (-) then it
will not find the part number in the table array even though they look
identical.
I have tried formatting the cells in various ways but it has made no
difference.
Any ideas??
Thanks,
Tony


--
anthonyn
------------------------------------------------------------------------
anthonyn's Profile:
http://www.excelforum.com/member.php...o&userid=16753
View this thread: http://www.excelforum.com/showthread...hreadid=319553

I've never had trouble with dashes. Is it possible that you have numeric values
formatted to include dashes either in the table or in the lookup value (with the
opposite being text?)



anthonyn wrote:

I am using vlookup to lookup quantities attached to part numbers. The
formula works fine unless the part number contains a dash (-) then it
will not find the part number in the table array even though they look
identical.
I have tried formatting the cells in various ways but it has made no
difference.
Any ideas??
Thanks,
Tony

--
anthonyn
------------------------------------------------------------------------
anthonyn's Profile: http://www.excelforum.com/member.php...o&userid=16753
View this thread: http://www.excelforum.com/showthread...hreadid=319553

--

Dave Peterson

You're welcome (from us) !
Glad to hear that you got it working
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"anthonyn" wrote

I think a formatting problem is being caused by me copying
and pasting the lookup data from Acrobat then having excel
generated data in the table array.
I stripped the dashes out of the table array data and then
applied Max's fix to clean up the data from Acrobat and
this seems to have fixed the problem.

Thank you both for you responses, they were of great assistance
Tony