software

I am trying to do a vlookup, I have alist of part numbers that are text
and some are numbers. The aray is going to a list with the same values
that are text and numbers to give me a text value.
=VLOOKUP(A144,[Book1]Sheet1!$A$1:$D$17174,4,FALSE)

When it looks up the numbers on the list it gives me my lookup value,
when it gets to the text part numbers ex (S#######/#####A) I get a #n/a
error. Why am I getting this error? Is there something I can use that
will lookup text and numbers that are on the same list?--
robertjtucker
------------------------------------------------------------------------
robertjtucker's Profile: www.excelforum.com/member.php...oamp;userid=21805
View this thread: www.excelforum.com/showthread...hreadid=509540Try a combination of the INDEX and MATCH functions
=INDEX([Book1]Sheet1!$A$1:$D$17174,MATCH(A144,[Book1]Sheet1!$A$1:$A$17174,0),4)
or
=INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(A144,[Book1]Sheet1!$A$1:$A$17174,0))

If you have numbers stored as text you will have to go a step further
=IF(ISNA(INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(A144,[Book1]Sheet1!$A$1:$A$17174,0))),INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(value(A144),value([Book1]Sheet1!$A$1:$A$17174),0)),INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(A144,[Book1]Sheet1!$A$1:$A$17174,0)))
which is an array formula so commit using Control Shift Enterquot;robertjtuckerquot; gt;
wrote in message
news:robertjtucker.22vobb_1139347205.6164@excelfor um-nospam.com...
gt;
gt; I am trying to do a vlookup, I have alist of part numbers that are text
gt; and some are numbers. The aray is going to a list with the same values
gt; that are text and numbers to give me a text value.
gt; =VLOOKUP(A144,[Book1]Sheet1!$A$1:$D$17174,4,FALSE)
gt;
gt; When it looks up the numbers on the list it gives me my lookup value,
gt; when it gets to the text part numbers ex (S#######/#####A) I get a #n/a
gt; error. Why am I getting this error? Is there something I can use that
gt; will lookup text and numbers that are on the same list?
gt;
gt;
gt; --
gt; robertjtucker
gt; ------------------------------------------------------------------------
gt; robertjtucker's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21805
gt; View this thread: www.excelforum.com/showthread...hreadid=509540
gt;
A little further testing shows me that these first two options I offered
don't improve your original formula but the third does (there must be values
stored as text). Also, VLOOKUP does not seem to accept the VALUE(your_range)
when I try to commit it as an array function, so you will have to use the
INDEX and MATCH combination (until someone else shows us differently)

=IF(ISNA(INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(A144,[Book1]Sheet1!$A$1:$A$17174,0))),INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(value(A144),value([Book1]Sheet1!$A$1:$A$17174),0)),INDEX([Book1]Sheet1!$D$1:$D$17174,MATCH(A144,[Book1]Sheet1!$A$1:$A$17174,0)))

which is an array formula so commit using Control Shift Enter