Index Match to find fields based upon Other Worksheets

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.

Put this formula in B6 and copy down. Don't try to expand the ranges to full columns, this is a calc-intense formula, the longer the range the more the sheet will slow down.

=INDEX(AReport!$C$4:$C$3000, MATCH($A$2&"-"&$A6, INDEX(AReport!$B$4:$B$3000&"-"&AReport!$G$4:$G$3000, 0), 0))

Just delete the cells with errors, those weren't found.


If you want the formula to deal with the errors, I'd suggest you switch to Excel 2007, then use the IFERROR option:

=IFERROR(INDEX(AReport!$C$4:$C$3000, MATCH($A$2&"-"&$A6, INDEX(AReport!$B$4:$B$3000&"-"&AReport!$G$4:$G$3000, 0), 0)), "")

Thank You, that works great!

Any thoughts on the second part of the issue?

CREPORT
1. Is there an equation I can put in cell A28 to return the Location Value located in Column C:C of the CReport for each location that has the Item # entered in cell A2

B24: =SUMIF(B6:B23,"<>#N/A", B6:B23) (solves the N/A issue for items not found)

B26: =COUNTIF(CReport!D:D, $A$2&"")

B28: =IF(ROWS($A$1:$A1)>$B$26, "", INDEX(CReport!$C$1:$C$500, SMALL(IF(CReport!$D$1:$D$500=$A$2&"", ROW($A$1:$A$500)), ROWS($A$1:$A1))))
(this is an array formula, enter it and confirm by pressing CTRL-SHIFT-ENTER to activate the array. Then copy down.)

C28: =IF(ROWS($A$1:$A1)>$B$26, "", INDEX(CReport!$E$1:$E$500, SMALL(IF(CReport!$D$1:$D$500=$A$2&"", ROW($A$1:$A$500)), ROWS($A$1:$A1))))
(this is an array formula, enter it and confirm by pressing CTRL-SHIFT-ENTER to activate the array. Then copy down.)

I applied conditional formatting to A28:A32 and B28:B32 to color the blank cells a different color.

Thank you, I am going to study this and see if I can follow the logic. But this is Brilliant!