Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE PLEASE INDEX MATCH OR SOMETHING ELSE ??
I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls).
Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 (PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. I have tried this VLOOKUP formula below which works for finding the product codes but not the batch codes; and it only works for the first 2 sheets and not the final 4 sheets.......HELP !!! IF(OR('[Prices.xls]sheet1'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet1'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet2'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet2'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet3'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet3'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet4'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet4'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet5'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet5'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet6'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet6'!$A$2:$C$3,3,FALSE),"")))))) PLEASE Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index and match | Excel Worksheet Functions | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |