Excel/Excel 2010 - formula problem
I am trying to find a solution to extract data from a table so long as two criteria match but one of those will change from month to month.
So from the basic table below, I will select the month of March from a drop down listing. Then I want my formula to give me the data by account for that month selected.
My problem is I don't know how to arrange the formula so that the months column is picked up automatically without having to change the formula each month to pick up the next applicable column.
The table of data will not be able to be edited to add in new columns.
I hope this makes sense (I have tried to word this as best I can).
Select Month from drop down Mar
Table of data.
Ac No Jan Feb Mar Apr
1000 52.6 426.8 2.6 5.7
2000 3.95 100 4587 200
3000 -36 5 1570 5
4000 33 145 11 5367
Required output when March is selected from the drop down.
assume the month names are in the first row and the dropdown is in M2 and the account numbers are listed in M3 to M6
in cell N3:
then drag fill that down column N.
The A2 in the row function will return a 2 as the second argument to the Offset function (minus 1 from that give a 1). The offset from the first argument in the offset function is zero based. so offset($A$1,0,0,1,1) is A1. Offset($A$1,1,0,1,1) is A2 and offset($A$1,0,1,1,1) is B1 just to illustrate.
That assumed it is picking up accounts in the same order as listed. If you want to match the account number in M3 then
so there is a match in both the row and column position.
Then select N3 and drag fill down column N.
Tested in with the stated situation and worked for me.