=Maxif

[AndrewD04]

Hi,

I am trying to get the maximum number of full weeks if the name matches but it doesn't seem to work appreciate any help

Basically i have 2 tabs of data.
1st tab has

2nd tab

what i am trying to do is a Maxif formula to return the max full weeks from tab 2 in to column E in Tab one if the names match in column A of both tabs everytime i do it i get #Value or 0

 

[alansidman]

If you have Excel version 2010 or later then you can use Power Query. Please update your profile to indicate the version you are using.

With PQ, you can Group the 2nd tab on the Agent for the Max.

Then you can join (merge in PQ terms) the two tables with a left inner join to put the Max Value in Column E. Once this is done in the PQ editor, you can then close and load to Native Excel.

If you would like specific Mcode that demos this, then post your sample data to this site using XL2BB so that we don't have to recreate your data to solve your issue.

 

[AhoyNC]

Is this what you want. It helps if we know what version of Excel you are using.
If your version of Excel doesn't have the MAXIFS function use the formula below which must be entered as an array with CTRL-SHIFT-ENTER.

MAX(IF(A2=Sheet2!$A$2:$A$10,Sheet2!$B$2:$B$10))

Book2
	A	B	C	D	E
1	Advisor	Pay	TM	GM	Full Weeks
2	Agent 1	1			6
3	Agent 2	2			2
4	Agent 3	3			5
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=MAXIFS(Sheet2!$B$2:$B$10,Sheet2!$A$2:$A$10,A2)

 

[AhoyNC]

Here is what Sheet2 is in the example I gave above.

Book2
	A	B
1	Agent	Full weeks
2	Agent 1	1
3	Agent 1	2
4	Agent 1	3
5	Agent 1	4
6	Agent 1	5
7	Agent 1	6
8	Agent 2	1
9	Agent 2	2
10	Agent 3	5
Sheet2