I can't reproduce a #VALUE error (if that is what you mean by "yields
value"). However, I think you might need the 4th parameter to VLOOKUP
set to FALSE. E.g.,

="Ext. No. "&VLOOKUP(C19,Z71:AB76,3,FALSE)

If you omit the FALSE or set it to TRUE, VLOOKUP does a "closest
match" lookup, and the data must be in ascending sorted order. With
FALSE, you indicate to VLOOKUP that you want an exact match. The order
of the data need not be sorted.

Also, ensure that the cell you are looking up, C19, does not itself
contain an error.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 7 Feb 2010 07:34:01 -0800, oldjay
wrote:

="Ext. No. "&VLOOKUP(C19,Z71:AB76,3)yields "Value"

=VLOOKUP(C19,Z71:AB76,3) yields the correct result