vlookup on 2 sheets for min or max date in an adjacent column

[m09151014]

I have a workbook with 3 sheets (sheet1, sheet2, sheet3) sheet1 has stores numbers in column A. Sheet2 and sheet3 also have store numbers in column A. I need it to find the store number in sheet2 or/and sheet3 for the store number in column A then give me the min or max date that corresponds with store number in column A in column E on sheet2 or sheet3. Is that possible?

 

[Joe4]

Does each store only show up one time on each sheet?

 

[m09151014]

Maybe a picture would help explain a little better. This would be my end result... I don't know how to make it look at both sheets for the numbers and then to add in the Min or Max formulas.

 

[Joe4]

Can you please update your user profile so we can see what version of Excel you are using?
There is a new function that makes this much easier to do (MAXIFS), but it won't do much good to suggest it if you do not have it.

 

[m09151014]

Does each store only show up one time on each sheet?

No stores can be on each sheet multiple times. But they will not be on both sheets.

 

[m09151014]

Does each store only show up one time on each sheet?

No stores can be on each sheet multiple times.

 

[m09151014]

Can you please update your user profile so we can see what version of Excel you are using?
There is a new function that makes this much easier to do (MAXIFS), but it won't do much good to suggest it if you do not have it.

Updated, I have O365, I have researched the MAXIFSs but, I just can't get it I guess.... THANKS for your help!

 

[Joe4]

Updated, I have O365, I have researched the MAXIFSs but, I just can't get it I guess....

I can get the MAXIFS one to work. For a value of sheet 1 in row 3, this formula seems to work:
=MAX(MAXIFS(Sheet2!E:E,Sheet2!A:A,Sheet1!A3),MAXIFS(Sheet3!E:E,Sheet3!A:A,Sheet1!A3))

MINIFS seems to be a bit more challenging, as when it is not found, it is returning a zero.
However, if what you say here is really true:

No stores can be on each sheet multiple times. But they will not be on both sheets.

Then we could to this (for an entry on Sheet1 in row 3):
=MINIFS(Sheet2!E:E,Sheet2!A:A,Sheet1!A3) + MINIFS(Sheet3!E:E,Sheet3!A:A,Sheet1!A3)

But I see that "3" appears in column A on both Sheets 2 and 3 in your example.
Not sure if that is a typo, or what you said about the number won't appear on both sheets is incorrect.

 

[CA_Punit]

Book1
	A	B	C	D
2	Sheet1
3			MIN	MAX
4	1		10/07/2020	14/07/2020
5	2		15/07/2020	19/07/2020
6	3		20/07/2020	08/11/2020
7	4		08/04/2020	08/04/2020
8	5		08/06/2020	08/06/2020
9	6		#NUM!	#NUM!
10	7		#NUM!	#NUM!
11
Sheet1

Cell Formulas
Range	Formula
C4:C10	C4	=AGGREGATE(15,6,CHOOSE({1,2},IF(Sheet1!A4=Sheet2!$A$3:$A$8,Sheet2!$C$3:$C$8),IF(Sheet1!A4=Sheet3!$A$2:$A$15,Sheet3!$C$2:$C$15)),1)
D4:D10	D4	=AGGREGATE(14,6,CHOOSE({1,2},IF(Sheet1!A4=Sheet2!A3:A8,Sheet2!C3:C8),IF(Sheet1!A4=Sheet3!A2:A15,Sheet3!C2:C15)),1)

 

[CA_Punit]

Book1
	A	B	C
1	Sheet 2
2
3	3		08/08/2020
4	3		08/09/2020
5	3		08/10/2020
6	3		08/11/2020
7	4		08/04/2020
8	5		08/06/2020
9
10
11
Sheet2

Book1
	A	B	C
1	Sheet 3
2	1		10/07/2020
3	1		11/07/2020
4	1		12/07/2020
5	1		13/07/2020
6	1		14/07/2020
7	2		15/07/2020
8	2		16/07/2020
9	2		17/07/2020
10	2		18/07/2020
11	2		19/07/2020
12	3		20/07/2020
13	3		21/07/2020
14	3		22/07/2020
15	3		23/07/2020
16
17
Sheet3

Cell Formulas
Range	Formula
C3:C15	C3	=C2+1