Originally Posted by
greghsa
Excel fundi's please help.
I have an Excel pricelist worksheet (.xls Excel 2003, 300 lines) with the following columns:
Code Price1 Price2 Price3
I also have another worksheet(.xls Excel 2003, 2000 lines) with the following columns:
Code Description
I want to modify first worksheet or create a third worksheet that will have:
Code Description Price1 Price2 Price3
Copy and paste or sorting will not work as different number of lines.
All codes in first worksheet are in second worksheet.
I would like excel to look for same code in different worksheet and pull the description .
Hope this makes sense. Any ideas would be appreciated
Ok, here is a demo
Code:
Set up Book1 Sheet 1 as follows: (Save it and keep it open)
code price1 price2 price3
code1 price1_1 price2_1 price3_1
code2 price1_2 price2_2 price3_2
code3 price1_3 price2_3 price3_3
code4 price1_4 price2_4 price3_4
Set up Book2 Sheet 1 as follows: (Save it and keep it open)
code descr
code1 descr1
code2 descr2
code3 descr3
code4 descr4
Set up Book3 Sheet 1 as follows:
code desr price1 price2 price3
=+[Book1.xls]Sheet1!A2 =VLOOKUP(A2,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE) =+[Book1.xls]Sheet1!B2 =+[Book1.xls]Sheet1!C2 =+[Book1.xls]Sheet1!D2
=+[Book1.xls]Sheet1!A3 =VLOOKUP(A3,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE) =+[Book1.xls]Sheet1!B3 =+[Book1.xls]Sheet1!C3 =+[Book1.xls]Sheet1!D3
=+[Book1.xls]Sheet1!A4 =VLOOKUP(A4,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE) =+[Book1.xls]Sheet1!B4 =+[Book1.xls]Sheet1!C4 =+[Book1.xls]Sheet1!D4
=+[Book1.xls]Sheet1!A5 =VLOOKUP(A5,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE) =+[Book1.xls]Sheet1!B5 =+[Book1.xls]Sheet1!C5 =+[Book1.xls]Sheet1!D5
Just substitute your book & sheet names, then correct your columns, rows & cell range for the vlookup.
Look carefully at the cell range, return column and exact match specifyer in the vlookup.
Cell range is top left where code starts and right bottom where description ends.
Return column is 2 because column 1 is the code to be looked up and column 2 is the description to be returned.
Exact match is set to FALSE otherwise it will return the nearest match which may not make sense. It is better to be able to convert a missing description to "MISSING DESCRIPTION" rather than a incorrect value
You can set up the missing description check as follows:
=IF(ISNA(VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE)),"MISSING DESCRIPTION",VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE))
Remeber to correct all the cell references
...and there you go
Did you like this article? Share it with your favourite social network.