How to reference data based on formula Result

[Bsh97]

HELLO EVERYONE !

Hope all had a great day ...

I 'm new to this forum and i need a guidance in creating a formula to reference/showing list the actual data based on this formula result

=SUMPRODUCT((MONTH(Table1[DateofHire])=J71)*(YEAR(Table1[DateofHire])=2020),--ISNUMBER(SEARCH($E$71,Table1[Department])))

I have attached a sample of what i mean in this regard ...


Thank you in advance

[protonLeah]

Thank you for the clarification

[protonLeah]

You don't have any data in row 71 on any sheet.....
Are you just trying to return a list of personnel hired on/after some date?

Also, you have a long formula, vis.,

Please Login or Register  to view this content.

in C8 & H8 to return a month name. It can be replaced with:

Please Login or Register  to view this content.

[protonLeah]

There's no reason for those merged cells.

New hire list***:
C10

Please Login or Register  to view this content.

D10

Please Login or Register  to view this content.

***no merged cells
***Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[Bsh97]

There's no reason for those merged cells.

New hire list***:
C10

Please Login or Register  to view this content.

D10

Please Login or Register  to view this content.

***no merged cells
***Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

It works perfectly Thank you very much

but i'm not sure how to drag the cell with your formula to show the rest of the list

Also i'm curious to know what is the reason in referencing the cell A1 in the formula ?

[protonLeah]

IF((MONTH(Table1[DateofHire])=G3)*(YEAR(Table1[DateofHire])=2020),ROW(Table1[DateofHire]))
Returns the row numbers of the values that satisfy the test conditions (there could be gaps that return NA's). This array is input into the SMALL function as the first parameter. The second parameter, ROW(A1) just returns the row number of cell A1, i.e., 1. So the SMALL will return data in the cell with the smallest row number. As you drag the formula down, A1 increments to A2 (2), A3(3), etc. So the result is a list starting with the first value found, second value, etc.