Lookup max value in a group of matching row, but return text in adjacent column

AAMN2014 · Oct 13, 2014

Hi Guys,

I've been scratching my head on this formula past few days.

How do I lookup a column with the highest value in a group of row? Example of table as below with the most right column the expected result of the formula (cant attach a file or past a picture, so the table might look a bit haywire).

I've been trying nesting Max/IF, with Index/Match, even with Vlookup functions, still not able to get a result I want.

I know if I wanted to get the Highest Loan Amount in each group, I'm able to use Max(IF) function, but not able to Index it and get the column adjacent to it.

Really hope you guys can help me out with this, have been bugging my work few days already. Thanks

1	A	B	C	D	E
2	GROUP	LOAN AMT	NAME	Person with Highest Loan Amt in each group	Expected Result
3	A	$10	Amy		Alex
4	A	$15	Adam		Alex
5	A	$20	Alex		Alex
6	B	$25	Bryan		Barry
7	B	$30	Barney		Barry
8	B	$35	Barry		Barry
9	C	$40	Cathy		Candice
10	C	$45	Cammy		Candice
11	C	$50	Candice		Candice

ChrisBM · Oct 13, 2014

Let's break this down.

To find the max in a range:

Code:

=Max(B3:B6)

To return the position in a range where the max appears:

Code:

=MATCH(Max(B3:B6), B3:B6,0)-1

To return the cell next to it:

Code:

=Offset(B3, Match(Max(B3:B6),B3:B6,0)-1,1)

Now all that remains is to match the group to a range of rows:
To find the beginning of the range:

Code:

 =MATCH("A", A:A,0)

To find the end of the range:

Code:

 =MATCH("A", A:A,0)+Countif(A:A, "A")-1

Putting it all together:

Code:

=Offset(INDIRECT("B" & MATCH("A", A:A,0)), Match(Max(INDIRECT("B" & MATCH("A", A:A,0) & ":B" & MATCH("A", A:A,0)+Countif(A:A, "A")-1)),INDIRECT("B" & MATCH("A", A:A,0) & ":B" & MATCH("A", A:A,0)+Countif(A:A, "A")-1),0)-1,1)

Hope that helps,

Kind Regards,
Chris

n.b. Make sure that in column B you have just numbers btw

Aladin Akyurek · Oct 13, 2014

GROUP	LOAN AMT	NAME	Person with Highest Loan Amt in each group
A	$20	Amy	Amy, Alex
A	$15	Adam	Amy, Alex
A	$20	Alex	Amy, Alex
B	$25	Bryan	Barry
B	$30	Barney	Barry
B	$35	Barry	Barry
C	$40	Cathy	Candice
C	$45	Cammy	Candice
C	$50	Candice	Candice

D2, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=REPLACE(aconcat(IF($A$2:$A$10=$A2,IF($B$2:$B$10=MAX(IF($A$2:$A$10=$A2,$B$2:$B$10)),
  ", "&$C$2:$C$10,""),"")),1,2,"")

ACONCAT is a VBA function, which you need to install as a module in your workbook, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

AAMN2014 · Oct 13, 2014

Thanks a lot, Chris & Aladin!

Chris, i tried your formula, unfortunately it's returning #N/A in the resulting column.

Aladin, I've created the ACONCATE module in VBA, unfortunately the resulting column is showing blank.

Appreciate all the help you guys can give on this. Thanks again!

1	A	B	C		D	E
2	GROUP	LOAN AMT	NAME	Method	Person with Highest Loan Amt in each group	Expected Result
3	A	$10	Amy	Aladin		Alex
4	A	$15	Adam	Chris	#N/A	Alex

Aladin Akyurek · Oct 13, 2014

AAMN2014 said:
...Aladin, I've created the ACONCATE module in VBA, unfortunately the resulting column is showing blank.

...

See the workbook that implements the set up with the ACONCAT function:
https://dl.dropboxusercontent.com/u/65698317/AAMN2014%20aconcat.xlsm

AAMN2014 · Oct 13, 2014

Wow! It works like a charm! I just realize my mistake in trying this out before, my workbook still had the "A-B-C-D-E & 1-2-3-4-5-6-7-8-9" taking up 1 row and 1 column that making the formula not match.

Thanks Aladin! Really out of the box solution, and taught me something new. Cheers!

1	A	B	C		D	E
2	GROUP	LOAN AMT	NAME	Method	Person with Highest Loan Amt in each group	Expected Result
3	A	$10	Amy	Aladin		Alex
4	A	$15	Adam	Chris	#N/A	Alex
5	A	$20	Alex			Alex
6	B	$25	Bryan			Barry
7	B	$30	Barney			Barry
8	B	$35	Barry			Barry
9	C	$40	Cathy			Candice
10	C	$45	Cammy			Candice
11	C	$50	Candice			Candice

Aladin Akyurek · Oct 13, 2014

AAMN2014 said:
Wow! It works like a charm! I just realize my mistake in trying this out before, my workbook still had the "A-B-C-D-E & 1-2-3-4-5-6-7-8-9" taking up 1 row and 1 column that making the formula not match.

Thanks Aladin! Really out of the box solution, and taught me something new. Cheers!
...

You are welcome. I don't understand the exhibit you post for it's not what the set up I posted would return!...

Fazza · Oct 13, 2014

If you can rely on the data being sorted, what about, in D2 and copy down,

Code:

=IF(A2<>A3,C2,D3)

AAMN2014 · Oct 13, 2014

Aladin Akyurek said:
You are welcome. I don't understand the exhibit you post for it's not what the set up I posted would return!...

Aladin, it was a stupid mistake on my part. But your solution is working perfectly how I wanted!

Below is the latest return I got by using your solution. When I tried your solution the first time, I forgot to remove the dummy row & column I created just to paste the table in this forum. So when I copy exactly the formula that you gave, it wasn't reading the right column.

But when I got your workbook, together with the VBA module you sent, I realized I forgot to remove it.

Anyway, thanks again.

GROUP	LOAN AMT	NAME	Person with Highest Loan Amt in each group
A	$10	Amy	Alex
A	$15	Adam	Alex
A	$20	Alex	Alex
B	$25	Bryan	Barry
B	$30	Barney	Barry
B	$35	Barry	Barry
C	$40	Cathy	Candice
C	$45	Cammy	Candice
C	$50	Candice	Candice

AAMN2014 · Oct 13, 2014

Fazza said:
If you can rely on the data being sorted, what about, in D2 and copy down,

Code:

=IF(A2<>A3,C2,D3)

Hi Fazza,

Unfortunately, I cannot rely on the data being sorted right every time.

Lookup max value in a group of matching row, but return text in adjacent column

AAMN2014

New Member

Excel Facts

ChrisBM

Board Regular

Aladin Akyurek

MrExcel MVP

AAMN2014

New Member

Aladin Akyurek

MrExcel MVP

AAMN2014

New Member

Aladin Akyurek

MrExcel MVP

Fazza

MrExcel MVP

AAMN2014

New Member

AAMN2014

New Member

Similar threads

Forum statistics

Share this page

Lookup max value in a group of matching row, but return text in adjacent column

New Member

Excel Facts

Board Regular

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock