Excel Matrix lookup
 

I have three matrices(see below), I would like to have excel lookup based on
the column and row(ex. [3,7]) and search the other matrices for the same
column and row numbers and add them together(ex. [3,7] + [3,7] = Y+ J).
However I am not sure how to go about doing this. Any help would be greatly
appreciated.


[1 2 5 6]
A B D E [1]
B E H F [2]
J U T D [5]
U Y P P [6]

[2 3 6 7]
A B D E [2]
B E G F [3]
J S D D [6]
U Y P P [7]

[3 4 7 8]
A B D E [3]
B E G F [4]
J S D D [7]
U Y P P [8]

Excel Matrix lookup
 

Assumptions:

A2:E6 contains the first matrix

A8:E12 contains the second matrix

A14:E18 contains the third matrix

Formula:

=SUM(IF(A2:D2=3,IF(E3:E6=7,A3:D6)),IF(A8:D8=3,IF(E 9:E12=7,A9:D12)),IF(A14
:D14=3,IF(E15:E18=7,A15:D18)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Matt wrote:

I have three matrices(see below), I would like to have excel lookup based on
the column and row(ex. [3,7]) and search the other matrices for the same
column and row numbers and add them together(ex. [3,7] + [3,7] = Y+ J).
However I am not sure how to go about doing this. Any help would be greatly
appreciated.


[1 2 5 6]
A B D E [1]
B E H F [2]
J U T D [5]
U Y P P [6]

[2 3 6 7]
A B D E [2]
B E G F [3]
J S D D [6]
U Y P P [7]

[3 4 7 8]
A B D E [3]
B E G F [4]
J S D D [7]
U Y P P [8]

Excel Matrix lookup
 

=INDEX(MATCH(..),MATCH(...))+INDEX(MATCH(..),MATCH (...))+INDEX(MATCH(..),MATCH(...))

Dave

--
Brevity is the soul of wit.


"Matt" wrote:

I have three matrices(see below), I would like to have excel lookup based on
the column and row(ex. [3,7]) and search the other matrices for the same
column and row numbers and add them together(ex. [3,7] + [3,7] = Y+ J).
However I am not sure how to go about doing this. Any help would be greatly
appreciated.


[1 2 5 6]
A B D E [1]
B E H F [2]
J U T D [5]
U Y P P [6]

[2 3 6 7]
A B D E [2]
B E G F [3]
J S D D [6]
U Y P P [7]

[3 4 7 8]
A B D E [3]
B E G F [4]
J S D D [7]
U Y P P [8]

Excel Matrix lookup
 

Thank you so much works great!!!!!!!!!

"Domenic" wrote:

Assumptions:

A2:E6 contains the first matrix

A8:E12 contains the second matrix

A14:E18 contains the third matrix

Formula:

=SUM(IF(A2:D2=3,IF(E3:E6=7,A3:D6)),IF(A8:D8=3,IF(E 9:E12=7,A9:D12)),IF(A14
:D14=3,IF(E15:E18=7,A15:D18)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Matt wrote:

I have three matrices(see below), I would like to have excel lookup based on
the column and row(ex. [3,7]) and search the other matrices for the same
column and row numbers and add them together(ex. [3,7] + [3,7] = Y+ J).
However I am not sure how to go about doing this. Any help would be greatly
appreciated.


[1 2 5 6]
A B D E [1]
B E H F [2]
J U T D [5]
U Y P P [6]

[2 3 6 7]
A B D E [2]
B E G F [3]
J S D D [6]
U Y P P [7]

[3 4 7 8]
A B D E [3]
B E G F [4]
J S D D [7]
U Y P P [8]