USING INDEX() WITH ARRAYFORMULA

Question

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?

Answer 1

Index doesn't work with ArrayFormula. You need to use VLookup().

=ArrayFormula(vlookup(C1:I1,{row(A1:A7),A1:A7},2))