VLookup always frustrates me

[kcdill]

I have a material takeoff for the structurals in one workbook. Cell E33 holds the name of the item "SqRect" and is called up with this formula. =IF(ISNA(MATCH(C33,'BT_Bridges & Supports for Fermenter Tank.xls'!sqrecttube_lbl,0)),"BOGUS","SqRect"). The lookup table is called SqRectTube_TBL and has two columns and is in a seperate workbook. The first column has the concatenated tube sizes such as 4x4x.25 and is named SqRectTube_LBL. The weight per foot for the tube is in the second column of the table and is called up with this formula =IF(E33="sqrect",VLOOKUP(C33,'BT_Bridges & Supports for Fermenter Tank.xls'!sqrect_TBL,2)*G33,"BOGUS"). As you can see the LBL and the TBL are not self capitized in the formulas like they shouild be. There are other tables on the same tables worksheet and they work just fine with the same formulas. The "SqRectTube" table is a new table and was just added to the tables worksheet. I can't put the tables in the same workbook as the material takeoff because of the file size. Any ideas?

 

[barry houdini]

You don't really say what the problem is, presumably your formulas aren't returning the values you expect. What results do you get, error messages, zero, wrong figures?

You say that the lookup table is called SqRectTube_TBL yet that isn't the name you use in the VLOOKUP formula

Your MATCH formula looks for an exact match (it has a 3rd argument of 0) but the VLOOKUP doesn't have a 4th argument of 0, you probably need to add that, i.e.

=IF(E33="sqrect",VLOOKUP(C33,'BT_Bridges & Supports for Fermenter Tank.xls'!sqrect_TBL,2,0)*G33,"BOGUS")

 

[kcdill]

Good Morning Barry:
Thanks for getting back to me. Sorry about the lack of information.
1. The named ranges SqRect_LBL and SqRect_TBL do not appear in the list of named ranges on the material spreadsheet. I think this condition is key to my problem. They do appear in the list on the TBL_SquareRectangularTubing worksheet.
2. C33 is where I input the Tube size. However no matter what size I put in C33, even if fictitious I always see "SqRect" in E33. The formula in E33 is =IF(ISNA(MATCH(C33,sqrect_TBL,0)),"BOGUS","SqRect").
3. P33 is where I would see the tube weight. The tube weight will be found in the SqRect_TBL in column 2 with this formula =IF(E33="sqrect",VLOOKUP(C33,'BT_Bridges & Supports for Fermenter Tank.xls'!sqrect_TBL,2,0)*G33,"BOGUS"). The value returned by this formula is #NAME?.