Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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] |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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] |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
Where to find complex matrix math add-ins for Excel 2003? | Excel Worksheet Functions | |||
How can i build up a Matrix in EXCEl ? | Excel Worksheet Functions | |||
How do I print a file holder label from Excel to a dox matrix pri. | Excel Discussion (Misc queries) |