Using arrayformula in Google Sheets

Question

I'm trying to use arrayformula to expand the formula below but it's not working. When I copy-paste manually the formula into each cell it works.

=arrayformula(INDEX($1:$100,ROW(B2:B),match(YEAR(M$1:$1),$1:$1,0)))

What I want to achieve is to convert yearly salaries into monthly salaries based on years.

Thanks!

CMB · Accepted Answer · 2021-03-05 17:28:54Z

1

Solution:

Since you are looking for a specific column, you can use HLOOKUP as a substitute for INDEX and MATCH:

=ARRAYFORMULA(HLOOKUP(YEAR(M1:AT1),A1:H11,ROW(B2:B11))/12)

This should yield the same result as the previous formula:

answered Mar 5, 2021 at 17:28

CMB

5,1811 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1