Find largest value in a column depending on value in another

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
-
 

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,297
Members
414,224
Latest member
Crazy_FC

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