Match

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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

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
 
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
 
Back
Top