CODEWITHSUNDEEP
excelindexingbooleanmaxmatch
topstuff
topstuff

Reputation: 129

Excel - INDEX/MATCH with MAX and Criteria - Boolean logic

enter image description here

Please see screen grab.
I am using Excel 2010. My result is needed in cell G4 which I will fill down to G14.
I am looking at the left table to match "Swindon" and the MAX value of "Net". So in this example, I would expect the result to be "Walters" from the "Name" column.
I am attempting to use two INDEX and a MATCH with some boolean logic on the two internal arrays on the second INDEX where it matches 1. However I cannot seem to get this formula working. Where am I going wrong? Please note: I am avoiding an array formula.
Thanks for looking.

Upvotes: 0

Views: 3077

Answers (2)

Andreas
Andreas

Reputation: 23958

If the values are unique as you say then you could use MAXIFS, INDEX and MATCH:

=INDEX(B:B,MATCH(MAXIFS(D:D,C:C,F4),D:D,0))

Find the max value where Swindon is in column C then use this value in index match.

enter image description here

2010 version would be AGGREGATE instead of MAXIFS:

=INDEX(B:B,MATCH(AGGREGATE(14,6,D:D/(C:C=F4),1),D:D,0))

enter image description here

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36860

With Excel2010 try below array formula-

=IFERROR(INDEX(Table6[Name],MATCH(MAX(IF(Table6[Location]=[@City],Table6[Net],""))&[@City],Table6[Net]&Table6[Location],0)),"")

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.

With Excel365 you can try-

=@IFERROR(SORT(FILTER(Table6,Table6[Location]=[@City]),3,-1),"")

enter image description here

Upvotes: 0

Related Questions