I have a list of data on A1:a7 (blue range) and i have a REF on D2:H2 (green range). I want to use the REF to search in the list where the row is equal to the REF.
The example result is on D3:H3
I put this formula in D3 (pink cell) :
=index(transpose($A$1:$A$7), 1 , D12)
then copy it across E3 to H3. This is the result i want to achieve but i want to do it using single formula in D3 . I tried using arrayformula() but it only returns single value.
=arrayformula( index(transpose($A$1:$A$7), 1 , D12:H12 ) )
How to use this index() inside the arrayformula?