Range lookup in Calc?

[peskatoon]

In Calc, how can I look up a value in row 1 of a range, and return the matching column from that range? As far as I know, HLOOKUP can return a single cell in that column, but not the whole column.

Say I have a data table at A1:Z100. I want to look up “XYZ” in A1:Z1. Then, if I find “XYZ” at C1, I want to return C2:C100. This is for the purpose of calculating correlation between the data in two columns, so I need to be able to insert the function(s) returning C2:C100 into CORREL.

Thanks!

[mariosv]

Pls take a look to the attached file, with a formula like:

{=INDEX(A2:C8;MATCH(B10;A2:A8;0))}

It is an array formula so use [Ctrl+Shift+Enter] instead only [Enter]

RecuperarUnaFilaDatos.ods