Whats wrong with this formula

[oldjay]

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

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

 

[Mike H]

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.

 

[dlw]

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]

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?

 

[Chip Pearson]

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]

 

[oldjay]

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

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]

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

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

.

 

[oldjay]

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.

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]

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

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

.

 

[oldjay]

Opps I mean Excel 2007

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.

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]

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

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

.