Correlation matrix to be populated from a 4 col table -tried if, match, and, index, arrray

Hi all, I've got a correlation matrix that I have to populate using values from another source. I've tried a combination of IF, MATCH, AND, arrays and all but I can't seem to get the desired result.

Arrays can't quite work (or can it) because the dimensions of the source and destination are different. There's also the fact that the correlation matrix is symmetrically diagonal but I can't seem to account for that in my formula.

Any help is much appreciated.

Hi, if the names (in columns ABC) were numbers not text, it would be easeier to do in one array formula (but anyway a bit complicated).

For this format of data I'd suggest a helper table with a following layout

ColA ColB colAvsColB
immediately followed by
ColB ColA 1/colAvsColB

(formulas to produce such helper table would be more than simple - see attachment)

then formula in your correlation matrix (in B12) could be (assuming the helper table starting A30):

=IF($A12=B$11,1,SUMIFS($C$30:$C$41,$A$30:$A$41,$A12,$B$30:$B$41,B$11))

See attachment
light blue cells -> formulas to produce helper table, copied down
dark blue Correlation Matrix formula, copied down and right

I am trying to find a simple way without helper table.

First start, working with a cell in side the matrix, B13, with value B (cell A13) in vertical and A (cel B11) in horizontal.

1) Finding which row that the combination of A&B is located. With the current cell is in B13, Ctrl-F3 to create a user-define name:

Pos = IFERROR(MATCH($A13&B$11,$A$2:$A$7&$B$2:$B$7,0),MATCH(B$11&$A13,$A$2:$A$7&$B$2:$B$7,0))

This formula should return 1, that means A&B, or B&A combination is located in row 1

2) Finding the "refer value":

=INDEX($C$2:$C$7,Pos)

3) Finding the "how much" number:

=INDEX($D$2:$D$7,Pos)

4)Compare the "refer value" with cell A13, if equal, return "how much" number, if not, return 1/"how much" number:

=IF(INDEX($C$2:$C$7,Pos)=$A13,INDEX($D$2:$D$7,Pos),1/INDEX($D$2:$D$7,Pos))

5)To return 1 to other cells if error found:

=IFERROR(IF(INDEX($C$2:$C$7,Pos)=$A13,INDEX($D$2:$D$7,Pos),1/INDEX($D$2:$D$7,Pos)),1)

Copy cell B13 to the whole matrix.

Hope this help and waiting for advice from other Expert and Guru.