Using arrayformula in Google Sheets

1
votes

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.

The spreadsheet can be viewed here: https://docs.google.com/spreadsheets/d/1veiYh1CMIfFPwBGQk4OwKmCLa7q08TugReVfAXtpIgI/edit#gid=1363287956

Thanks!

1
google-sheetsgoogle-sheets-formulaarray-formulas
The sheet doesn't allow access. – Tom Sharpe
Sorry it's fixed! – Jey Jay

1 Answers

1
votes

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:

enter image description here