I have two sheets, one with month/year data spilt across the row from I2 using the formula =EDATE(DATE(2017,8,1), SEQUENCE(1,401,0)) and another with month and expenditure data running down the columns.
I’d like to insert a spill formula in to I3 in the first sheet to lookup the month from I2# with the corresponding month in the second sheet and if found, multiply the values in columns D and E.
With the data being in rows on one sheet and columns in the other I’m not sure how to tackle this.
The following is a sample of the data in the second sheet:
| Month | Projected Costs | Actual Costs | Monthly Employees | Monthly Per Employee Costs |
|---|---|---|---|---|
| Nov-19 | £2,000 | £3,000 | 5 | £600 |
| Dec-19 | £10,000 | 11 | £909 | |
| Jan-20 | £18,000 | 19 | £947 | |
| Feb-20 | £18,000 | 19 | £947 | |
| Mar-20 | £18,000 | 19 | £947 | |
| Apr-20 | £18,000 | 19 | £947 | |
| May-20 | £18,000 | 19 | £947 | |
| Jun-20 | £18,000 | 19 | £947 | |
| Jul-20 | £18,000 | 19 | £947 | |
| Aug-20 | £18,000 | 19 | £947 | |
| Sep-20 | £18,000 | 19 | £947 | |
| Oct-20 | £18,000 | 19 | £947 |
>Solution :
By using I2# correctly in your formula, the output will be the same size/dimensions as I2#. One way of doing it would be separate lookups on columns D and E respectively which are then multiplied together.
=XLOOKUP(I2#, Sheet1!A:A, Sheet1!D:D) * XLOOKUP(I2#, Sheet1!A:A, Sheet1!E:E)
(You might wish to add an IFERROR() function around the outside of that to clear up all the months where data isn’t found)