Need help with creating a formula. I have tried several different ways to include ifs/xlookups/index-matches but I think I might be in over my head. I am needing to update my Kit Summary sheet with the new numbers in the Order sheet. I have to match the Kit Names in column A and the Kit Numbers in column B of the Kit Summary with the Kit Names in Column A and Kit Numbers in row 1 of the Order Sheet. On top of that, I have to also match the Ordering years on the Kit Summary sheet (row 1) with the ordering years that correspond on the Order Sheet in the first column. I am having a huge issue since one spreadsheet is setup more vertically and the other spreadsheet is setup more horizontally. Any help is always appreciated. Thank you.

Attached Files

Book6.xlsx (13.8 KB, 7 views)

In the attached, there's a formula in cell C2 of the Kit Summary sheet:
=myQty($A2,$B2,C$1,'Order Sheet'!$A$1:$BE$8)
it can be copied across and down to cell L16.
Are the values returned correct?

Attached Files

msofficeforums52081Book6.xlsx (16.9 KB, 5 views)

I am so sorry I said that the wrong way around. I need the numbers from the Kit Summary sheet into the Order sheet, but I may be able to reverse it to the other sheet once I understand what the formula is doing. Thanks so much for your help!

See pivot table at cell G11 of the Order Sheet.

Attached Files

msofficeforums52081Book6b.xlsx (27.9 KB, 4 views)

Thank you so much! This works.