Lookup Multiple Values, Return one value, sum its value and placed in another sheet

[bjnockle]

I believe there is a formula to achieve the desired outcome I hope for. I have a table (named HomeGame) that contains Forecast and Actual data spanning Monday through Sunday. In the subject heading, there are Branding (A7), Content Marking (A8), Pricing (A9), Promotion (A10), Branding (A11), Promotion (A12), Pricing (A13), Content Marking (A14) and Branding (15) - all selected from a dropdown list. This list can change when depending on the dropdown value in cell B2.

In column B through O, there are weekdays (Monday through Sunday) with Forecast and Actual subheading under each heading. In the AwayGame Table, the goal is to have a formula vertically lookup the “SUBJCET” heading in column A of the HomeGame table, look through the values for ACTUAL instead of forecast and return one value (e.g. Branding), sum the values for all the returned value for Monday (for example, Branding) and placed in weekday Monday. Repeat the same lookup formula for the remaining values and have their values summed for Monday through Sunday.

Sample workbook provided for your convenience. The Desired Outcome is provided in worksheet AwayGame in the sample workbook.

Thank you for your assistance in advance.

[alansidman]

Look at the attached.

[bjnockle]

Alan, thank you for the prompt response. I did not debug/use any code in the workbook. It only has dropdown menu in B2 to select the desired value and that populates the subject heading below. Thank you in advance for your assistance. I am looking for a formula to lookup the table in HomeGame sheet and populate AwayGame sheet.

[benishiryo]

hi bjnockle. another alternative. in A6, try this array formula:
=IFERROR(INDEX(HomeGame!$A$7:$A$20,MATCH(0,COUNTIF(A$5:A5,HomeGame!$A$7:$A$20),0))&"","")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

in B6, copy down & across:
=SUMPRODUCT((HomeGame!$A$7:$A$20=$A6)*(HomeGame!$A$5:$O$5=B$5)*(HomeGame!$B$6:$P$6="Actual"),HomeGame!$B$7:$P$20)

[bjnockle]

benishiryo: Thank you so so so much for the two formulas - works like a charm in pulling the values from HomeGame sheet into AwayGame sheet. However, I noticed that when you delete the values under the subject heading in HomeGame workeet, it returns blank cells under subject in AwayGame worksheet while the weekdays retain constant numbers. Is there a way to have the weekday cells return blanks when there are no values in the subject heading HomeGame worksheet. I have attached the sample sheet for your convenience. Thanks in advance for your assistance.

[benishiryo]

glad it helps. i should have added this too:
=IF($A6="","",SUMPRODUCT((HomeGame!$A$7:$A$20=$A6)*(HomeGame!$A$5:$O$5=B$5)*(HomeGame!$B$6:$P$6="Actual"),HomeGame!$B$7:$P$20))

[bjnockle]

benishiryo: WOW, You are a genius! Your solution is nothing but a 5 star solution! Thanks for your time and efforts!

[bjnockle]

benishiryo: Is there a way to have this formula (=IFERROR(INDEX(HomeGame!$A$7:$A$20,MATCH(0,COUNTIF(A$5:A5,HomeGame!$A$7:$A$20),0))&"","")) modified for Excel 2003 to accommodate IF ISERROR? I am getting the #N/A error because Excel 2003 does not have the IFERROR statement. Thanks for your assistance in advance.