vlookup

Lanny,

Your description is a bit terse. Sounds like that your lookup table is expanding by additions. Care to provide more info along with the 'affected' VLOOKUP formula?

Aladin

Sorry. I have one workbook that is nothing but pricing info on several sheets. I have another workbook that I use for estimating. I copy the first cell from my pricing speadsheet to a cell in the estimating spreadsheet. I have recently started adding new items in the pricing sheet but vlookup doesnt find the data The formula is =IF(A12=0," ",VLOOKUP(A12,'A:\Communication\Sales Department\[Communication Price List.xls]Work Stations'!$1:$65536,2,FALSE))

Lanny,

Copy Communication Price List.xls to your hard drive, open it from ther, go to the first cell of your 2-column data on Work Stations.

Lets say that the first cell is A1. Activate the option Insert|Name|Define. Enter PRICES as Names in Workbook and the following formula as Refers To:

==OFFSET('Work Stations'!$A$1,0,0,COUNT('Work Stations'!$A:$A),1).

Click Add. Again enter PRICES as Names in Workbook and the following formula as Refers To:

=OFFSET('Work Stations'!$A$1:$B$1,0,0,COUNT('Work Stations'!$A:$B),2)

Click OK.

These formulas expect that there are no holes/blank cells in columns A and B.

Change your VLOOKUP formula to:

=IF(A12>0,IF(ISNUMBER(MATCH(A12,'COmmunication Price List.xls'!LVALUES,0)),VLOOKUP(A12,'COmmunication Price List.xls'!PRICES,2,0),""),"")

Note that 0 means FALSE.

Aladin

PS. I'd suggest not to use a file driectly from a floopy to avoid future problems.

==============

Thanks

Lanny -- There is a typo: the first PRICES should have been LVALUES. I hope you figured that out. Cheers. -Aladin