Correlation table

**dow400** · 03-08-2013, 09:22 AM

Hello,

I have a big correlation table to 30 different datasets. I would like to create a second table in which I enter the instruments of my choice in the first row and column and subsequently the table vlookups the given values from the bigger correlation table.

I have attached the file for ease of convenience.

Appreciate your help

**Special-K** · 03-08-2013, 09:43 AM

in B38
=INDEX($B$3:$AE$32,MATCH($A38,$A$3:$A$32,0),MATCH(B$37,$B$2:$AE$2,0))

and copy across and down as far as M49

**ChemistB** · 03-08-2013, 09:49 AM

In B38, copied across and down
=INDEX(CorrTable,MATCH($A38,$A$3:$A$32,0),MATCH(B$37,$B$2:$AE$2,0))
where CorrTable = =Sheet1!$B$3:$AE$32
Does that work for you?

On Index
http://www.techonthenet.com/excel/fo...x_function.php

**dow400** · 03-08-2013, 10:20 AM

Special K and ChemistB, thx for the input - it works great. ChemistB you named the range Sheet1!$B$3:$AE$32as CorrTable. How can I make this a dynamic range so that when I add a new instrument, i.e one new row and one new column CorrTable includes the new entries as well?

Best

**ChemistB** · 03-08-2013, 11:16 AM

This would work

=Sheet1!$B$3:INDEX(Sheet1!$B$3:$BV$74, COUNTA(Sheet1!$A$3:$A$74),COUNTA(Sheet1!$B$2:$BV$2))

Note: I moved your second table to start in column B so that it wouldn't interfer with the COUNTA(A3:A74)