# Find largest value in a column depending on value in another

#### LoriD

##### Board Regular
I have 2 sheets. "EquipmentTable" and "MaintRecords". (Each time a piece of equipment is serviced, it is entered into MaintRecords.) In my "Equipment table", all equipment is listed. I want to pull in the current hours from the "MaintRecords" sheet for each unit, into the EquipmentTable, but don't know how. I've been trying to combine the DMAX with VLOOKUP to get this, but I really am not sure if it's even possible. Can anyone tell me how to do this? Thanks.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
On 2002-09-24 07:53, LoriD wrote:
I have 2 sheets. "EquipmentTable" and "MaintRecords". (Each time a piece of equipment is serviced, it is entered into MaintRecords.) In my "Equipment table", all equipment is listed. I want to pull in the current hours from the "MaintRecords" sheet for each unit, into the EquipmentTable, but don't know how. I've been trying to combine the DMAX with VLOOKUP to get this, but I really am not sure if it's even possible. Can anyone tell me how to do this? Thanks.

Could you describe a very concise example.
- do you have to calculate the max
- do you have to total information
- what columns have descriptions and amounts
-

No, I don't need totals, and I don't think I need to calculate MAX, I just want to bring over from the MaintRecords sheet, whatever the largest value is in the column for a given piece of equipment.
Sheet "EquipmentTable" lists Unit#, Serial#,Make,Model,etc. ColumnA holds the unit number. So if cell A1 is Unit# WY1 I want to display in columnF, the current Hours on the equipment, and in columnG, the current Miles. Those would come from the MaintRecords sheet. Unit #WY1 will be listed many times, once for each time it is serviced. At each service, the current miles and hours are recorded in columns J & K. So I want to look up the largest mileage value in columnJ and hour value in columnK for Unit WY1, and have it on the EquipmentTable worksheet. I hope I haven't confused the issue here!! Thanks again.

On 2002-09-24 11:18, LoriD wrote:
No, I don't need totals, and I don't think I need to calculate MAX, I just want to bring over from the MaintRecords sheet, whatever the largest value is in the column for a given piece of equipment.
Sheet "EquipmentTable" lists Unit#, Serial#,Make,Model,etc. ColumnA holds the unit number. So if cell A1 is Unit# WY1 I want to display in columnF, the current Hours on the equipment, and in columnG, the current Miles. Those would come from the MaintRecords sheet. Unit #WY1 will be listed many times, once for each time it is serviced. At each service, the current miles and hours are recorded in columns J & K. So I want to look up the largest mileage value in columnJ and hour value in columnK for Unit WY1, and have it on the EquipmentTable worksheet. I hope I haven't confused the issue here!! Thanks again.

(1.) Name the range LTable where the lookup table is.
(2.) Now use:

=SUMPRODUCT(MAX((INDEX(LTable,0,1)=lookup-value)*(INDEX(LTable,0,10))))

for Miles. Change 10 to 11 for Hours.

EDIT: Reminded by Dave's post, jusr drop superfluous SUMPRODUCT and just array-enter:

=MAX((INDEX(LTable,0,1)=lookup-value)*(INDEX(LTable,0,10)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.
This message was edited by aladin akyurek on 2002-09-24 12:52

On 2002-09-24 11:18, LoriD wrote:
No, I don't need totals, and I don't think I need to calculate MAX, I just want to bring over from the MaintRecords sheet, whatever the largest value is in the column for a given piece of equipment.
Sheet "EquipmentTable" lists Unit#, Serial#,Make,Model,etc. ColumnA holds the unit number. So if cell A1 is Unit# WY1 I want to display in columnF, the current Hours on the equipment, and in columnG, the current Miles. Those would come from the MaintRecords sheet. Unit #WY1 will be listed many times, once for each time it is serviced. At each service, the current miles and hours are recorded in columns J & K. So I want to look up the largest mileage value in columnJ and hour value in columnK for Unit WY1, and have it on the EquipmentTable worksheet. I hope I haven't confused the issue here!! Thanks again.

with criteria in a cell ( I used A50)

array enter with Ctrl-Shift-Enter

=MAX(IF(MaintRecords!A2:A100=A50,MaintRecords!J2:J100))

Revise the references to fit your information.

You could try a Pivot Table.

Thank you!

Thank you!

You may still want to try Pivot Tables
Set data to MAx not Sum

You can still access particular numbers via
formula.

Max of Miles
Unit Total
Unit# WY1 520
XYZ 1500

unit name in A11 and formula below in B11

Miles
Unit# WY1 520

B11 =GETPIVOTDATA(A3,""&A11)

Replies
0
Views
291
Replies
21
Views
457
Replies
3
Views
458
Replies
3
Views
362
Replies
14
Views
365

1,203,673
Messages
6,056,677
Members
444,881
Latest member
Stu2407

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