Title: Dynamically Lookup Value with Between - Excel
Tags: excel-formula;lookup
Question: I have a chronological list of Product, Year, Month, Profit (like below).
Summary Table
Product Year    Month   Profit
TV      2018    1       10
TV      2018    2       20
TV      2018    3       30
TV      2018    4       50
TV      2018    5       35
TV      2018    6       60
TV      2018    7       90
Heater  2018    1       20
Heater  2018    2       3
Heater  2018    3       8
Heater  2018    4       4
Heater  2018    5       6
Heater  2018    6       11
Heater  2018    7       1
What I wanted to do is lookup another sheet that has all of the price changes within by month and year as well as the table below shows.
Sale Price
Product Year    Month   Price
TV      2018    1       $1,000.00
TV      2018    4       $800.00
TV      2018    7       $950.00
Heater  2018    1       $20.00
Heater  2018    2       $60.00
Heater  2018    5       $45.00
So the end result for example, TV Month = 2 and Year = 2018, I want it to pull in $1,000 to be part of my profit calculation.
Comment: so your output would be what and where would it go?

Comment: So you want the price to show up in a new column next to the profit column in the first table?

Comment: @ScottCraner The output will be used for my profit calculation Where if my profit = Sales Price - SUM(operating cost + product cost + employee cost)

Comment: @ScottCraner I want to pull it into my profit equation, where the other 3 costs are static, but sales price is the only variable that can change.

Here is the accepted answer: to get the correct Price, use:
=INDEX(J:J,AGGREGATE(14,6,ROW($I$2:$I$7)/(($G$2:$G$7=A2)*($H$2:$H$7=B2)*($I$2:$I$7<=C2)),1))
Comment for this answer: then please consider marking as correct by clicking the check mark by the answer.
Comment for this answer: @Kevin that is a new question but look into the arrayformula(large(if()))
Comment for this answer: I was trying to transfer my excel sheet to Google sheet but they do not have the aggregate function, would you know if there is an alternative to aggregate?


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2018:00:07/05/18', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<excel-formula><lookup>', 'Title': 'Dynamically Lookup Value with Between - Excel', 'ViewCount': 308, 'id': 51199851, 'user_AboutMe': None, 'user_AccountId': 13459517.0, 'user_DisplayName': 'Kevin', 'user_Reputation': 3.0, 'user_Views': 6.0, 'user_id': 9712059.0}}