# Return value pairs based on first row and column

Gnumeric has a nice tool for calculating correlations, but is limited in terms of specifying your output. It looks like this, Figure 8-7 :

``````Correlations | pressure | distance | height
pressure     |        1 |          |
distance     | 0.9841073|        1 |
height       | 0.7633996| 0.7239509|      1
``````

I would like to generate pairs like this:

``````pressure pressure  1
pressure distance  -
pressure height    -
distance pressure  0.9841073
distance distance  1
distance height    -
height pressure    0.7633996
height distance    0.7239509
height height      1
``````

I'm fine with 1's and blanks, but it would be nice if I could avoid them. It doesn't have to be within a spreadsheet.

This is a pretty straightforward `Index/Match`:

``````=INDEX(\$B\$2:\$D\$4,MATCH(\$A6,\$A\$2:\$A\$4,0),MATCH(\$B6,\$B\$1:\$D\$1,0))
``````

Assuming your data is like the below: • This doesn't work in Excel 2010 or Gnumeric (I know to substitute commas with semicolons with it). Apr 3 '18 at 11:44
• @anon8723879278 - This should work in Excel 2010...what happens when you try to use it? Apr 3 '18 at 14:34
• "Circular reference warning" Apr 3 '18 at 14:43
• @anon8723879278 - How is your worksheet setup? That warning means you're referencing the cell the formula is actually in. In my example, I put the formula in `C6`. You will likely need to adjust yours. This formula will work in Excel 2010, it's just you need to tweak the ranges to match your data. Apr 3 '18 at 14:55
• It works when I input the variable names in preceding cells. Part of my question was about automatically generating these "pressure pressure, pressure distance" pairs. I apologise for not making that clear enough. Apr 3 '18 at 15:13