VLookup to return MAX value from multiple hits

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.

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

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

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

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

Jon von der Heyden said:

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.

Click to expand...

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?

Joyner said:

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?

Click to expand...

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.

crackod93 said:

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

Click to expand...

Can you please tell me what did you use?

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?