# Finding max value in a mixed data set.

#### chuggins143

##### Board Regular
Good morning one and all! I'm working on a sorting problem that someone here might be able to shed some light on... I have two columns of data, the first column contains voltages. A mixture of 120, 208, and 240 values in no particular order, and in column two there are various amp draw values. I've been trying different ways to look down through the data set and pull the max amp draw for each voltage with little success. I've attempted a couple of variations with Lookup, Index, Match, and so forth, but am coming up dry. Anyone ever run into this and possibly have a solution they might be willing to share?
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.

#### JamesW

##### Well-known Member
Hi there,

Have you tried a pivot table?

Voltage = Row Area
Amps = Data Items Area (set to MAX instead of SUM).

#### chuggins143

##### Board Regular
Honestly, I've never used pivot tables... I'll take a look and see.
C

#### bryonwoods30

##### Board Regular
ok say your data is in column a and b. highlight both column and sort by a ascending and b ascending.

in c1,c2,c3 put 120,208,240.

then in c1 use the formula =VLOOKUP(C1,A:B,2,1) and it will return the max amps number for each

#### chuggins143

##### Board Regular
Ok, I'm trying to get this data into a pivot table, and it looks like I did it... got the amp values reset to Max, and it pulls the correct amp value when I creat the table, but it doesn't update if I go into the list and change anything. Is there a way to have it active?
C

#### chuggins143

##### Board Regular
ok say your data is in column a and b. highlight both column and sort by a ascending and b ascending.

in c1,c2,c3 put 120,208,240.

then in c1 use the formula =VLOOKUP(C1,A:B,2,1) and it will return the max amps number for each

There's the rub, I can't sort because my data is being looked up from somewhere else so I have to look for it where it's currently located. If it was only 5 or 10 points then it wouldn't be that big of a deal, but I have several thousand data points I'm sifting through.

#### bryonwoods30

##### Board Regular
if they are formulas and being looked up from somewhere else. just copy both columns and paste special then the values on a new sheet. then sort and do the vlookup

#### chuggins143

##### Board Regular
Ok, one of our guys here at work got it figured out... to do it he used an array formula and named ranges... Name the volts column "volts" and the amps column "amps" and use =MAX(IF(Volts=Target,Amps,0)) where Target is a named cell where I can put in 120, 208, or 240 and once you put in the formula do a <Ctrl> + <Shift> + <Enter> and then there should be {} around the formula so it looks like this on the formula bar... {=MAX(IF(Volts=Target,Amps,0))} ...it updates properly and works quite nicely. Just remember every time you go to that cell it resets to a regular formula so you have to hit <Ctrl> + <Shift> + <Enter> to make it an array formula! I'd never used array formulas before so I guess I learned something today!
Thanks for all the responses!
C

#### chuggins143

##### Board Regular
Ok, one of our guys here at work got it figured out... to do it he used an array formula and named ranges... Name the volts column "volts" and the amps column "amps" and use =MAX(IF(Volts=Target,Amps,0)) where Target is a named cell where I can put in 120, 208, or 240 and once you put in the formula do a <ctrl> + <shift> + <enter> and then there should be {} around the formula so it looks like this on the formula bar... {=MAX(IF(Volts=Target,Amps,0))} ...it updates properly and works quite nicely. Just remember every time you go to that cell it resets to a regular formula so you have to hit <ctrl> + <shift> + <enter> to make it an array formula! I'd never used array formulas before so I guess I learned something today!
Thanks for all the responses!
C

Apparently I used some bad symbols or something... to enter the formula as a array formula use Ctrl + Shift + Enter

</enter></shift></ctrl></enter></shift></ctrl>

Replies
1
Views
360
Replies
0
Views
1K
Replies
0
Views
292
Replies
1
Views
188
Replies
1
Views
382

1,190,696
Messages
5,982,343
Members
439,775
Latest member
mathewduffy

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