PLEASE PLEASE INDEX MATCH OR SOMETHING ELSE ??

M

MichelleS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top