need to return maximum value among multiple records with same key using VBA

[sushaanth]

I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).
Eg :

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">sno dept name marks \br1 cs sush 55 \br2 ece ram 68 \br3 cs harish 77 \br</code></pre>
So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Try this array formula

=MAX(IF($B$2:$B$100=F2,$D$2:$D$100))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

Where F2 houses the value of interest (cs in your example)

M.

 

[sushaanth]

Hi and welcome to Mr Excel Forum

Try this array formula

=MAX(IF($B$2:$B$100=F2,$D$2:$D$100))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

Where F2 houses the value of interest (cs in your example)

M.

Thanks Marcelo,

Will this work in VBA ???

 

[Marcelo Branco]

Thanks Marcelo,

Will this work in VBA ???

You are welcome.

You dont need VBA, but if you want maybe something like this

Sub aTest()
    Dim myVar
    
    'Adjust the sheet name in red
    myVar = Evaluate("MAX(IF([COLOR=#FF0000]Sheet1[/COLOR]!$B$2:$B$100=[COLOR=#FF0000]Sheet1[/COLOR]!$F$2,[COLOR=#FF0000]Sheet1[/COLOR]!$D$2:$D$100))")
    
    MsgBox myVar
    
End Sub

M.

 

[sushaanth]

I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).
Eg :

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">sno dept name marks \br1 cs sush 55 \br2 ece ram 68 \br3 cs harish 77 \br</code>
So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.

Is there any way to compute the formulae without pressing ctrl+shift+enter. I have lot of records and doing this for each record is tiresome.

 

[sushaanth]

Hi and welcome to Mr Excel Forum

Try this array formula

=MAX(IF($B$2:$B$100=F2,$D$2:$D$100))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

Where F2 houses the value of interest (cs in your example)

M.

Is there any way to compute the formulae without pressing ctrl+shift+enter. I have lot of records and doing this for each record is tiresome.

 

[Marcelo Branco]

Is there any way to compute the formulae without pressing ctrl+shift+enter. I have lot of records and doing this for each record is tiresome.

Try this

=MAX(INDEX(($B$2:$B$100=F2)*$D$2:$D$100,0))

M.