VLookup to return MAX value from multiple hits

crackod93

Board Regular
Joined
Aug 9, 2007
Messages
71
Hi, can anyone help me with the below...

Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.

Lookup number 1 from column A, then give me the largest number from column B

example table:
A B
-----
1 5
2 2
1 11
3 2
4 5

the result would be 11

thanks all!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hello

Try:

=MAX(IF(A1:A100=1,B1:B100))

Commit the formula with CONTROL+SHIFT+ENTER (not just ENTER). If correcly committed the formula will be surrounded with braces {}.

Also, do not use whole columns , e.g. A:A and B:B.
 

crackod93

Board Regular
Joined
Aug 9, 2007
Messages
71
sorry, I should have been a little clearer.

The reason for the VLOOKUP requirement is because its across sheets and also because i have multiple lookups to do

i.e. sheet 1 contains unique reference numbers for the vlookup to use, sheet two contains multiple rows for which contain multiple occurances of these reference numbers, for example;

sheet 1

ref#:
1
2
3
4

sheet 2

ref# data to pull max from
1 10
2 10
1 15
3 10
2 22
4 10
1 9
2 11

in sheet 1, id need something like vlookup(ref#,sheet2!A:B,2,false) but because the reference number 1 is on sheet2 multiple times, i want it to return the largest value in column 2 from all possible matches

thanks
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
I think Jon's formula might still work, if you mod the formula slightly

=MAX(IF(sheetx!A1:A100=ref#,sheetx!B1:B100))

so if your ref was in cell A1 on sheet 'summary' and your data was on sheet 'data'

in B1, you could put

=MAX(IF('data'!A1:A100=A1,'data'!B1:B100))
 

crackod93

Board Regular
Joined
Aug 9, 2007
Messages
71

ADVERTISEMENT

my apologies, i didnt realise that you could do this calc without a vlookup, it appears to work fine, thanks so much!!
 

micbarnes

New Member
Joined
Aug 12, 2014
Messages
2
Hi all

I have used the above formula (thanks for sharing), and have noticed that Excel limits the number of rows in the formula to 709.

I.e. {=MAX(IF(A1:A709=1,B1:B709))}

At row 710 and above the formula no longer works.

Is anyone aware of a way to increase the searchable rows included in this formula to above 710?

Michael


Hello

Try:

=MAX(IF(A1:A100=1,B1:B100))

Commit the formula with CONTROL+SHIFT+ENTER (not just ENTER). If correcly committed the formula will be surrounded with braces {}.

Also, do not use whole columns , e.g. A:A and B:B.
 

Joyner

Well-known Member
Joined
Nov 15, 2005
Messages
1,202

ADVERTISEMENT

Hello Michael and welcome to the board. There should be no such limit. I am assuming you have one formula, but referencing 709 rows, but if you try to reference 710 rows (A1:A710) your formula stops workings?
 

micbarnes

New Member
Joined
Aug 12, 2014
Messages
2
Hello Michael and welcome to the board. There should be no such limit. I am assuming you have one formula, but referencing 709 rows, but if you try to reference 710 rows (A1:A710) your formula stops workings?


Hi Joyner, thanks for clarifying this. I have looked into the data further and have removed an #N/A in the last data populated row (904) and it now seems to be working using a column reference (A:A). I'm not sure why the formula stopped working at row 710 (and not at 904), but it is working now.

Thanks for your assistance.
 

scottta

New Member
Joined
Dec 1, 2016
Messages
1
I'm trying to get this to work myself, and this thread has been helpful, but it's still not working for me.
Here's what I'm trying to do: We have a spreadsheet that contain messages from Yammer groups in our company. I've got a summary spreadsheet which counts the number of messages from each group (based on a VLOOKUP on group ID). I'm trying to find the LATEST message posted for that group.

In my summary spreadsheet, I have this formula:
=MAX(IF('\\SCEA0APP2002\YammerData$\[Messages.xlsx]Messages'!$E$2:$E$65536=A3,'\\SCEA0APP2002\YammerData$\[Messages.xlsx]Messages'!$S$2:$S$65536))

So the Messages.xlsx workbook with the Messages worksheet has column E being the Group_ID and column S being the Created_At date that the message was logged. In my Summary workbook, column A has the Group_ID we're doing the lookup on and column G is where we're trying to put that latest message date.
So in cell G3, I put the above formula. So I'm looking at the E column in Messages and finding all the records that match up to the Group_ID in A3 (same row that the formula is in). $E$2:$E$65536=A3 Then I want to find the Max Date in the Created_At date in column S of Messages for all records for the Group_ID in A3 of the Summary sheet.

When I try to run that formula, the result I get is "0". What am I doing wrong?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,922
Latest member
defectexpress

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top