I have a worksheet that I need to return multiple fields based on multiple critera based upon information that is placed in another worksheet.
So basically, I will paste 2 seperate reports in different worksheets (AReport & CReport). And in a "Test" worksheet, I want to return values of cells based upon what Item # is entered in cell A2 in the "Test" worksheet.
*One other issue is the Item #'s are in TEXT format, is there an easy way to convert this to regular formatting
Below are the 2 issues:
1.AReport:
B6 needs to return the QTY value located in column C:C in the worksheet AReport if the following criteria are met:
a. Location # in this sheet (A6) = the location in column G:G in the worksheet AReport
b. Item # in this sheet (A2) = the SKU in column B:B in the worksheet AReport
=INDEX('AReport '!$C$4:$C$2981,MATCH(Test!A6,'AReport '!$G$4:$G$2981,0,MATCH(Test!A2,'AReport '!$B$4:$B$2981,0))
2.CReport
a. Is there an equation I can put in cell A28 to return the Location Value located in C:C of the CReport for each location that has the Item # entered in cell A2
=INDEX('CReport'!$C:$C,Test!A2,'CReport'!$D:$D,0)
But is it possible to put a formula in the next cell?
b. An equation similiar to the one above to return the Current Qty field
Any guidance would be appreciated. I have attached a Sample file.
Bookmarks