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

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

Hi,

It looks fine to me and tests out OK.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"oldjay" wrote:

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

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

is the result of the vlookup a number, or text? If it's a number, try
text(VLOOKUP(C19,Z71:AB76,3),1)
but when I do it, a number works too?

"oldjay" wrote:

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

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

When I copy the cell from the Main! sheet and insert it into a new sheet
="Ext. No. "&VLOOKUP(MAIN!C19,MAIN!Z71:MAIN!AB76,3) It works fine
I tried Text on the Main sheet same result(Value.
Don't ask me to recreate this workbook. It is 3.5mb with a couple thousand
lines of code

Any more ideas?

"dlw" wrote:

is the result of the vlookup a number, or text? If it's a number, try
text(VLOOKUP(C19,Z71:AB76,3),1)
but when I do it, a number works too?

"oldjay" wrote:

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

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

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

That didn't do it. What I did was copy the formula to another existing sheet
where it worked and them referenced that cell to the original one. You figure
"Chip Pearson" wrote:

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
.

This workbook is used for estimating and manufacturing process control and
has been used for many years. My problems have occurred since I tried to
migrate to Excel 2010. I opened the workbook in 2010 and saved it as an .xls
file compatible with older versions. The size of the file increased from
3.0mb to 3.6mb. It has numerous formula errors.
I have deleted all data and code except 2 cells with formula errors. I have
saved this workbook. It is now has 500K with only 2 cells populated. I
would like someone to look at this and give me some advise on what to do.



"Chip Pearson" wrote:

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
.

Opps I mean Excel 2007

"oldjay" wrote:

This workbook is used for estimating and manufacturing process control and
has been used for many years. My problems have occurred since I tried to
migrate to Excel 2010. I opened the workbook in 2010 and saved it as an .xls
file compatible with older versions. The size of the file increased from
3.0mb to 3.6mb. It has numerous formula errors.
I have deleted all data and code except 2 cells with formula errors. I have
saved this workbook. It is now has 500K with only 2 cells populated. I
would like someone to look at this and give me some advise on what to do.



"Chip Pearson" wrote:

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
.