# vlookup

Posted by Lanny Grimes on July 16, 2001 11:18 PM

I have a spreadsheet of values but when i add more rows the formula returns n\a how do i update the list of data

Posted by Aladin Akyurek on July 17, 2001 12:42 AM

Lanny,

Posted by Lanny Grimes on July 17, 2001 7:15 AM

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

Posted by Aladin Akyurek on July 17, 2001 10:17 AM

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.

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

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

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

Posted by lanny on July 17, 2001 2:52 PM

Thanks

Posted by Aladin Akyurek on July 17, 2001 3:10 PM

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