0

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.

EDS
  • 2,155
  • 1
  • 6
  • 21
TeelowxD
  • 27
  • 3

1 Answers1

0

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))).

EDS
  • 2,155
  • 1
  • 6
  • 21
  • One thing, In 2019 this needs to be entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode. Which is why I would suggest the AGGREGATE version on the dup link. It will do the same with the same speed, but avoid the need to use Ctrl-Shift-Enter. – Scott Craner Aug 11 '21 at 22:43
  • To add onto this question, is I am given a table name, is it possible to look from that table only. So the user will be given three parameters in this case: date, table name, and symbol. – TeelowxD Aug 11 '21 at 22:51
  • Also to add onto this question again, the formula does not work if the symbol has spaces included or if the symbol has a longer name. It works perfectly fine with 3 letters. Just not sure how to make it work if there are other characters involved in the symbol. – TeelowxD Aug 12 '21 at 20:42
  • Another question is how would this work with the filter function? – TeelowxD Aug 12 '21 at 20:43
  • @TeelowxD see my edits for an example of ```FILTER```. As to the longer names, not sure why that's the case, I would think it should work regardless. Can you give an example? – EDS Aug 12 '21 at 20:46
  • @Euler'sDisgracedStepchild For example, if I have a symbol as "123 17SEP21 60.3 A" and I want to filter for that symbol there appears to be an #N/A error. But the formula works perfectly fine for characters such as "ABC". I'm not sure if it has to do with the table perhaps. Thanks for your response. – TeelowxD Aug 12 '21 at 20:52
  • 1
    @Euler'sDisgracedStepchild My apologies, it appears to be working now. Thank you for your help. – TeelowxD Aug 12 '21 at 20:57