Using VSTACK and FILTER to take info in H1 (IF) and spit out C1

[jslocumb]

I am terrible at Excel but trying.

I have a workbook with multiple sheets. I want a formula I can put on Sheet 1 that gives me the following information:
If on Sheets 2-57, the number in H1 is 5 or greater, then give me the info in C1. This will spit out multiple Names from different sheets, and I want to see all of them listed.
I am assuming that you would use VSTACK with FILTER for this, but I can't fathom how.

H1 is itself a formula that gives me a number based on rolling calendar year.
C1 is the person's name.
Basically H1 tells me how many absences they have had in the last 365 days. C1 is their name. I want their name(s) to populate as a list in Sheet1 if the number of absences is 5 or greater.

 

[Dave Patton]

=LET(a,VSTACK(Sheet2:Sheet57!C1:H1),FILTER(a,INDEX(a,,6)>=5))

 

[Dave Patton]

For just the first column, try

=LET(a,VSTACK(Sheet2:Sheet57!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))

 

[Dave Patton]

Try the formulas with just 2 sheets
If you need both the name and the number, consider the second formula.

3D Sumif 365.xlsm
	A	B	C
1
2		Person A
3		Person B
4
5
6		Person A	7
7		Person B	10
8
3a

Cell Formulas
Range	Formula
B2:B3	B2	=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),TAKE(FILTER(a,INDEX(a,,6)>=5),,1))
B6:C7	B6	=LET(a,VSTACK(Sheet2:Sheet3!C1:H1),FILTER(CHOOSECOLS(a,1,6),CHOOSECOLS(a,6)>=5))
Dynamic array formulas.