Formula Help Needed

[carl]

My data table is like this:

Symbol Total 549S0A1 919S0B1 919S0A1
Total 245216 44296 41402 16053
QQQQ 20478 1009 2837 1558
MSFT 15754 2107 1857 250
SPY 5314 1143 198
AAPL 5212 231 1167 331

I am looking for a formula to put into the 3rd row below that will take the
value in row 1 and row 2 and return the corresponding value in the table
above - the result is shown below as well. For row 2 below, I was hoping the
formula would be able to look at just the first 3 characters of the table
above - so if there are multiple instances, the formula will sum up the
values.

QQQQ
919
=2837+1558 (4395)

Thank you in advance.

 

[Miguel Zapico]

You can see some examples of the INDEX and MATCH on Debra Dalgleish site:
http://contextures.com/xlFunctions03.html
The examples 2 and 3 for INDEX and MATCH show how to extract single values
from a table with a similar layout.
What it is more complex is getting the sum of the result with a simple
formula, you may need to do it in two steps (getting the values for the
individual codes and adding up)

Hope this helps,
Miguel.

 

[Domenic]

Assuming that A1:E6 contains the data, A10 contains QQQQ, and A11
contains 919, try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(A3:A6=A10,IF(LEFT(C1:E1,LEN(A11))+0=A11,C3:E6)))

Hope this helps!

 

[Peo Sjoblom]

=SUMPRODUCT((LEFT(C1:E1,3)=""&A10)*(A3:A6=A9)*(C3:E6))

where A1:E6 is the table, A9 holds QQQQ and A10 holds 919




Regards,


Peo Sjoblom