Vlookup

[kcdill]

Good Afternoon:
For some strange reason I just can't get my Lookup formula to find the Label or the Table.

The tables are in a Workbook named TBL_PIPE.
The SqRect_LBL is found at =TBL_PIPE!$AE$4:$AE$640.
The SqRect_TBL is found at =TBL_PIPE!$AE$4:$AH$640.
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.

This afternoon I copied the Table from the Workbook named TBL_PIPE to my Takeoff Workbook as an experiment. Now the Lookup formula seems to work just fine when the table is in the same Workbook as the formula. My Takeoff Workbook is Protected. I inherited the Protection from another person when I started working here.
Question: Is it possible that the Protection would prevent the formula from looking outside the Takeoff Workbook for data that is found in a different Workbook?

 

[Derek Brown]

It would be helpful to see the formula.
Does the second argument of the formula include the name of the other workbook?

 

[xenou]

the lookup LBL had a 0 in front of .375. I removed the 0 but no effect.

Sounds suspiciously like you have text in a cell that looks like a number (error checking might alert you to this).


To change text to numbers (where conversion is possible):
=Value(A1)

To change numbers to text:
=Text(A1,"general")

Usually, I do this in a column to the right, then Copy | PasteSpecial values back into the correct column.

Other troubleshoots are:
1) Leading and trailing spaces (with text lookups)
2) Decimal values that are different but not seen due to formatting (with number lookups)
3) Not using the proper Match argument (True for "tax-table" style lookups, False for exact match lookups)
4) Sometimes with named ranges you can successfully fix a glitch by re-naming the lookups with new names (if you've copied the tables from other workbooks or worksheets)

Regards