Vlookup of list with text and number

[robertjtucker]

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?

 

[Bob Tarburton]

Try 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+Enter


"robertjtucker" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Tarburton]

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