enter image description here

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.

index(transpose($A$1:$A$7), 1 , D12:H12 )

How to use this index() inside the arrayformula?

1 Answer 1


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


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.