Find largest value in a column depending on value in another

LoriD

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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,195
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
143
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,210
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
5,195
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
5,195
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,026
Messages
5,835,022
Members
430,332
Latest member
Charly_Moon

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