Find Max with 2 criterias

[Patcheen]

Hi Guys

Im using this formula

=MAX(IF(E2:E100,A2,IF('info Data'!AE2:AE100,C1,IF('info Data'!I2:I100,">0"))))

a2 is the name it needs to match in e2:e100 - E2:e100 is the employee names - Ae2:ae100 is the ware houses - i2:i100 deliveries made

C1 is the ware house name



i just want to find the max for Lets say john to dublin from column i2:i100

 

[Bosquedeguate]

the MAX function does not work for strings

Please provide more information.

From your description I think you have the following:
Col E (rows 2 to 100) contain names
Col AE (on a different sheet "info Data") contains warehouse names
Col I (rows 2 to 100 of "info Data") has a delivery made flag

You can do a VLOOKUP of the name in the Col E range of data as follows:
=VLOOKUP(A2, E2:E100, ?, false)

What are you doing with the match? If the name is found in the name list then what?

There is some information missing in your problem description.

 

[awoohaw]

Try the MAXIFS:

Mr excel questions 53.xlsm
	A	B	C	D	E	F
1	QAQQ	<<< Name to Match Deliveries Made >>	ANGC	names	DELIVERIES	warehouses
2				YPGT	74	YEBW
3		176		IBRO	87	NETV
4				IBRO	85	ANGC
5				IBRO	214	ANGC
6				YPGT	235	PFDQ
7				IBRO	170	PFDQ
8				CMMO	135	DZZT
9				CMMO	71	DZZT
10				KVQD	82	YEBW
11				KVQD	72	DZZT
12				IBRO	83	PFDQ
13				IBRO	186	YEBW
14				YPGT	169	KKHN
15				IBRO	118	ANGC
16				KVQD	119	DZZT
17				QAQQ	58	YEBW
18				IBRO	237	DZZT
19				IBRO	104	ANGC
20				CMMO	159	KKHN
21				QAQQ	176	ANGC
22				QAQQ	218	KKHN
23				KVQD	176	YEBW
24				IBRO	56	PFDQ
25				YPGT	206	ANGC
26				KVQD	223	NETV
27				KVQD	62	ANGC
28				QAQQ	161	NETV
29				IBRO	147	YEBW
30				CMMO	226	KKHN
31				IBRO	108	ANGC
32				KVQD	241	DZZT
33				QAQQ	176	YEBW
34				IBRO	164	ANGC
35				YPGT	179	DZZT
36				QAQQ	151	KKHN
37				YPGT	94	NETV
38				IBRO	72	NETV
39				YPGT	206	KKHN
40				KVQD	222	ANGC
41				KVQD	97	ANGC
42				KVQD	221	PFDQ
43				KVQD	232	KKHN
44				YPGT	197	YEBW
45				CMMO	140	DZZT
46				IBRO	229	ANGC
47				YPGT	228	PFDQ
48				CMMO	73	YEBW
49				IBRO	250	DZZT
50				YPGT	217	PFDQ
51				KVQD	129	ANGC
52				QAQQ	67	NETV
53				KVQD	190	ANGC
54				YPGT	213	DZZT
55				CMMO	229	PFDQ
56				YPGT	113	NETV
57				IBRO	108	PFDQ
58				CMMO	141	YEBW
59				IBRO	87	YEBW
60				IBRO	107	NETV
61				YPGT	191	YEBW
62				IBRO	63	DZZT
63				YPGT	53	KKHN
64				YPGT	156	ANGC
65				YPGT	135	YEBW
66				QAQQ	125	DZZT
67				YPGT	77	YEBW
68				CMMO	161	ANGC
69				KVQD	217	YEBW
70				IBRO	118	PFDQ
71				KVQD	179	YEBW
72				YPGT	169	NETV
73				QAQQ	97	PFDQ
74				KVQD	179	KKHN
75				IBRO	208	NETV
76				KVQD	87	KKHN
77				CMMO	239	PFDQ
78				QAQQ	215	NETV
79				IBRO	147	YEBW
80				YPGT	164	YEBW
81				IBRO	129	PFDQ
82				QAQQ	166	ANGC
83				CMMO	190	PFDQ
84				QAQQ	149	YEBW
85				IBRO	178	YEBW
86				QAQQ	214	PFDQ
87				YPGT	115	DZZT
88				YPGT	250	DZZT
89				QAQQ	248	DZZT
90				IBRO	226	YEBW
91				QAQQ	90	PFDQ
92				CMMO	137	DZZT
93				KVQD	65	PFDQ
94				IBRO	114	KKHN
95				QAQQ	178	KKHN
96				YPGT	222	KKHN
97				CMMO	154	DZZT
98				KVQD	74	DZZT
99				KVQD	92	KKHN
100				IBRO	210	PFDQ
Patcheen

Cell Formulas
Range	Formula
B3	B3	=MAXIFS($E$2:$E$100,$D$2:$D$100,A1,$F$2:$F$100,C1)
F2:F100	F2	=CHOOSE(RANDARRAY(1,1,1,6,1),"KKHN","NETV","ANGC","DZZT","PFDQ","YEBW")

 

[Patcheen]

Thank you so much guys this worked a treat ye are brill thank you again

 

[awoohaw]

Thank you so much guys this worked a treat ye are brill thank you again

You're welcome. If you think one of the posts here provided you with a solution, please mark the post with the solution as the answer.

Best wishes!