Search code examples
excelexcel-formulaexcel-2019

How to create a formula that takes information from a table for a specific date in time

Given symbol such as (123) and a date such as (2021-08-11), I would like to obtain specifically stat 1 or stat 2 from a separate table (Table name is "Table1"). The symbol can appear more than once on the same date in the table but in this case stat 1 and stat 2 will be the same number for each entry of the symbol. I would like the formula to grab only one of stat1 and stat2 in that case, not the summation.

Table 1

The output should look like this.

Result

The output should result in a formula in the highlighted cells that contain the stats for a specific date and symbol.

Solution

  • There are many different ways, but one is to simply use INDEX and MATCH

    For example: =INDEX($C$1:$D$6, MATCH(value1&value2, $A$1:$A$6&$B$1:&$B$6,0),)

    enter image description here

    If you have newer versions of Excel, you may find FILTER to be easier. For example, =FILTER($J$2:$K$5, (($H$2:$H$5=$H8)*($I$2:$I$5=$I8))).