Vlookup returning largest value

Question

I need help writing a formula that will do a vlookup and return the largest value - Example:

A table of data has account numbers - and the clients payment abouts are listed for each month.  I want to return the largest payment the client made:

Account         Month         Payment

1234               Jan               100.00

1234               Feb               5500.00

1234               March          14144.

In my new spreadsheet for 1234 I would want the vlookup to return the 14144 value since that was the largest payment.

Answer 1

Hi,

Create a Pivot Table.  Drag Account to the Row labels and Payment to the value area section.  Right click on any number in the value area section and select Max.

Hope this helps.

Answer 2

F2: {=MAX((F1=A2:A7)*C2:C7)}

Note:

The {} means this is an array formula.

Copy the formula without the {} into the formula bar and press CTRL-SHIFT-ENTER

After that the {} appears around the formula.

Andreas.

Answer 3

Let's assume that your account number is in sheet2. You can use following formula in this case

=MAX(INDEX((SHEET2!F1=A2:A100)*(C2:C100),,))

Answer 4

I was doing the vlookup as my list of acct numbers is on another tab - so this doesn't seem to be working for me)?

Answer 5

Building upon the formula suggested by Andreas where F1 has the account number, you can use following formula which doesn't require to be entered as Array

=MAX(INDEX((F1=A2:A100)*(C2:C100),,))