Search multiple columns for value, but within date range, return values of other column

[cjtraas]

Hi everyone. I've been pulling my hair out, getting close but still cannot get what I want to have happen.

My workbook contains multiple sheets. I need to search one sheet (or all 4 if possible), 3 columns, for a value - within a date range, and return a different value. Example below:

Book1
	A	B	C	D	E	F	G	H
1	12/27/2022	Smith	Mike	12/23/2022	4/6/2019	Hastings
2	12/27/2022	Jones	Katie	12/21/2022	6/26/1984			Chad
3	12/29/2022	Johnson	Nelly	12/27/2022	2/20/1974	Phillips
4	1/3/2023	Brown	Alexander	12/31/2022	4/28/1999		Chad
5	1/4/2023	Miller	Luis	1/2/2023	8/11/1995	Chad
6	1/4/2023	Davis	Marrisa	1/2/2023	6/30/2006	Phillips
7	1/4/2023	Williams	Ariana	1/2/2023	7/11/2012		Chad
8	1/5/2023	Burk	Leticia	1/4/2023	4/12/1961		Gulbransen
Sheet2

In this example, I would need to search for "Chad" in Sheet2 columns F:H, but within a date range in A:A (1/1/2022 - 3/24/2023), and return the names to Sheet 1 - from Sheet 2 column B (preferably "B, C" if possible). Example of what the output should look like.

Book1
	A
1	Brown, Alexander
2	Miller, Luis
3	Williams, Ariana
Sheet1

At an absolute minimum I would like to search Sheet 2 (3 columns) and get corresponding value from Sheet 2 column B - and output to Sheet 1 column A.
My dream - end goal - I need to search Sheets 2, 3, 4, 5 (same 3 cloumns on each sheet) and get corresponding values from Sheets 2, 3, 4, 5 cloumns B and C (LastName, FirstName) and output to Sheet 1 column A.

Any help or pointers in the right direction would be VERY appreciated. Thank you!

 

[cjtraas]

**Follow-up

So far, the following formula is working most of the way. I can perform what is needed, but searching only 1 sheet for now (will work on searching all 4 sheets).

Also, for some reference, Sheet1 is my main sheet and E2 is the Start Date, F2 is the End Date - for returning values between those 2 dates.
A1 is the text value I am searching for. Using the example in my first post, this would be "Chad".

If anyone can suggest something cleaner, better, more efficient - please feel free to reply. Thanks!

=TOCOL(IF(Sheet2!$A$2:$A$3000>=Sheet1!$E$2,IF(Sheet2!$A$2:$A2000<=Sheet1!$F$2,IF(Sheet2!$G$2:$I$3000=$A$1,Sheet2!$C$2:$C$3000&", "&Sheet2!$D$2:$D$3000,x),x),x),2)

 

[Fluff]

How about

=LET(v,VSTACK(Sheet2!A1:H100,Sheet3!A1:H100,Sheet4!A1:H100),TOCOL(IF((TAKE(v,,-3)=A1)*(INDEX(v,,1)>=E2)*(INDEX(v,,1)<=F2),INDEX(v,,3)&", "&INDEX(v,,2),1/0),2))

 

[cjtraas]

How about

=LET(v,VSTACK(Sheet2!A1:H100,Sheet3!A1:H100,Sheet4!A1:H100),TOCOL(IF((TAKE(v,,-3)=A1)*(INDEX(v,,1)>=E2)*(INDEX(v,,1)<=F2),INDEX(v,,3)&", "&INDEX(v,,2),1/0),2))

This helps a lot. Thank you. Quick question though. Using my example, searching for "Chad", how can I only search columns F:H in case one of the names in B:C might also contain "Chad"?

 

[Fluff]

It does only search columns F:H

 

[cjtraas]

It does only search columns F:H

Well I guess it's pretty clear that I don't understand how exactly it works! (facepalm..) I think I'm going to have to buy a book or something to learn this stuff much better. Again, Thanks for your help!

 

[Fluff]

You're welcome & thanks for the feedback.