MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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,

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

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.

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.

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


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