Column and Row lookup, skip if blank

[mattyblueice]

I have a table of data with months in the column header and rows for different depts. This is a simplified sample of what I am working with, but what I am trying to do is to have a formula look at a cell with that has the current month, scan the data in that month and if there is a value, ie Dept bring in that Dept name and then keep scanning the data set and bringing in values for depts with values in the current month. I have been researching how to do this with Index and Match with no luck. Any ideas would be great. Thank you.

 

[awoohaw]

try this;
(for dates, I used date values formatted as month and year)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Dept	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25
2	Admin				600
3	IT			400	300				1000
4	Exec		400
5	Finance								1000
6	Sales
7
8		Current Month	Apr-24
9
10	Dept	Apr-24
11	Admin	600
12	IT	300
Sheet2

Cell Formulas
Range	Formula
A10:B12	A10	=LET(a,CHOOSECOLS(A1:N6,1,MATCH(C8,A1:N1,0)),b,CHOOSECOLS(a,2),FILTER(a,b<>0,""))
Dynamic array formulas.

 

[mattyblueice]

Thank you for this I have never tried to use a variable to solve a problem in Excel, thank you so much!

 

[awoohaw]

You're welcome. Thanks for the feedback. There is probably a more succinct formula but this seems to work for your need.