I can't understand why my lookup formula can't find the SqRect_LBL values.
The cell in E100 should read SqTube. What am I doing wrong?
=IF(ISNA(MATCH(C100,'bt_rev_10-10-07.xls'!SqRect_LBL,0)),"BOGUS","SqTube")
Cell C100 = 4x4x.375.
The tables are in a workbook named TBL_PIPE.
The SqRect_LBL is found at =TBL_PIPE!$AE$4:$AE$520.
The SqRect_TBL is found at =TBL_PIPE!$AE$4:$AH$520.

I checked the text in the cells and the only difference I could find were
the column widths are different and the lookup LBL had a 0 in front of .375.
I removed the 0 but no effect. Cell E100 and the matching cell in my lookup
table are the same length.

The syntax of MATCH is =MATCH(lookup value, lookup array, [type]).

You don't have a lookup array.


On Nov 5, 2:24 pm, kcdilloo
wrote:
I can't understand why my lookup formula can't find the SqRect_LBL values.
The cell in E100 should read SqTube. What am I doing wrong?
=IF(ISNA(MATCH(C100,'bt_rev_10-10-07.xls'!SqRect_LBL,0)),"BOGUS","SqTube")
Cell C100 = 4x4x.375.
The tables are in a workbook named TBL_PIPE.
The SqRect_LBL is found at =TBL_PIPE!$AE$4:$AE$520.
The SqRect_TBL is found at =TBL_PIPE!$AE$4:$AH$520.

I checked the text in the cells and the only difference I could find were
the column widths are different and the lookup LBL had a 0 in front of .375.
I removed the 0 but no effect. Cell E100 and the matching cell in my lookup
table are the same length.

You don't have a lookup array.

The 2nd argument in the formula refers to 'bt_rev_10-10-07.xls'!SqRect_LBL

and the OP says

The SqRect_LBL is found at =TBL_PIPE!$AE$4:$AE$520.

Maybe SqRect_LBL is not a workbook-level name?

OTOH, if he created the formula by pointing to the range, the reference should be correct.

You may be correct that SqRect_LBL is a named range. I do not tend to
use named ranges and so do not think of them when I look at the syntax
of a function.

Dave

On Nov 5, 2:50 pm, Myrna Larson
wrote:
You don't have a lookup array.

The 2nd argument in the formula refers to 'bt_rev_10-10-07.xls'!SqRect_LBL

and the OP says

The SqRect_LBL is found at =TBL_PIPE!$AE$4:$AE$520.

Maybe SqRect_LBL is not a workbook-level name?

OTOH, if he created the formula by pointing to the range, the reference should be correct.

Hi,
If you're dealing with text then you might have extra spaces at the end, try
this:

=IF(ISNA(MATCH(TRIM(C100),TRIM('bt_rev_10-10-07.xls'!SqRect_LBL),0)),"BOGUS","SqTube")

HTH
Jean-Guy

"kcdilloo" wrote:

I can't understand why my lookup formula can't find the SqRect_LBL values.
The cell in E100 should read SqTube. What am I doing wrong?
=IF(ISNA(MATCH(C100,'bt_rev_10-10-07.xls'!SqRect_LBL,0)),"BOGUS","SqTube")
Cell C100 = 4x4x.375.
The tables are in a workbook named TBL_PIPE.
The SqRect_LBL is found at =TBL_PIPE!$AE$4:$AE$520.
The SqRect_TBL is found at =TBL_PIPE!$AE$4:$AH$520.

I checked the text in the cells and the only difference I could find were
the column widths are different and the lookup LBL had a 0 in front of .375.
I removed the 0 but no effect. Cell E100 and the matching cell in my lookup
table are the same length.