# VLookup to return MAX value from multiple hits

#### crackod93

##### Board Regular
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
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
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
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

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
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

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
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.

#### rahul2301

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

Can you please tell me what did you use?

#### scottta

##### New Member
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?

Replies
1
Views
120
Replies
4
Views
23
Replies
9
Views
94
Replies
3
Views
156
Replies
3
Views
351

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.

### Which adblocker are you using?

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

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