Getting values through index match but ignoring header and blank cells

[justme101]

Hello all,

I am trying to get values from sheet 2 to sheet 1, using index match, but the problem is that it is simply getting the value form 1st match, which might be blank and I do not need it. I am proving a sample of the sheet data below (sorry, can't post the excel file at the moment):

This is how the sheet 1 destination should look like based on where the X's are put in the grid in Sheet 2. The difference between these sheets is that Invoice and retail are in columns in sheet 2 but are pulled into rows in sheet 1. Should be a pretty clear example, but please let me know if this needs explanation.

 

[steve the fish]

Here would be one way:

=SUMIFS(INDEX(Sheet2!$C$6:$D$1000,,MATCH($C6,Sheet2!$C$5:$D$5,0)),Sheet2!$A$6:$A$1000,$A6,Sheet2!$B$6:$B$1000,$B6,INDEX(Sheet2!$E$6:$H$1000,,MATCH(D$5,Sheet2!$E$5:$H$5,0)),"x")

 

[justme101]

Here would be one way:

=SUMIFS(INDEX(Sheet2!$C$6:$D$1000,,MATCH($C6,Sheet2!$C$5:$D$5,0)),Sheet2!$A$6:$A$1000,$A6,Sheet2!$B$6:$B$1000,$B6,INDEX(Sheet2!$E$6:$H$1000,,MATCH(D$5,Sheet2!$E$5:$H$5,0)),"x")

This worked flawlessly on the sample data. Unfortunately, I modified the formula to be applied to the actual data sheet, and it did not work and I ain't smart enough to figure out the exact problem. I used the evaluate formula option as well to see where it went wrong, but it was not clear and suddenly I got the #Value error. I am modifying the sample data a little bit, to match the real one as close as possible. Basically, I am removing the Key column as it was something created by me for Vlookups. Kindly help me with an updated formula, if needed.

SOURCE:

DESTINATION: