EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
(OP)
Thank you for any help before hand. I am stumped on what to do here.
I am trying to lookup the maximum value with a if criteria in the maximum formula. I then want to know the location of the maximum value. The problem I am having is that when two values has the same maximum value, my formula will return the location of the first time the maximum value occurs. I have attached a pdf to better explain my issue. I will also be uploading my excel shortly.
I am trying to lookup the maximum value with a if criteria in the maximum formula. I then want to know the location of the maximum value. The problem I am having is that when two values has the same maximum value, my formula will return the location of the first time the maximum value occurs. I have attached a pdf to better explain my issue. I will also be uploading my excel shortly.
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
In your example, the two rows are adjacent.
Can you get multiple rows for some value that are not adjacent?
Okay, I had some time to snoop.
3C returns Max Y 1.8, Location 5. But here's what's in the table...
Location REACTIONS CODE X Y 5 1 2C 3.6 1.8 17 2 3C 0 1.8 69 5 2C 3.6 1.8 81 6 3C 0 1.8 133 9 2C 3.6 1.8 145 10 3C 0 1.8
So with that caveat, what you need is to use an MS Query Parameter query, that uses the MAX Y value as a parameter to select a list of Locations to use in a Date Validation --LIST source.
Skip,
for a NUance!
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
The Table is on the Data sheet and it has been made a Structured Table.
ENABLE MACROS to prepare the file.
Skip,
for a NUance!
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
I'd set up an extra column combining the text and the value, then you can find the maximum value and use match on the extra column with =(D1&D2) to return the row offset for the value you want.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
BTW, this technique is the basis for cascading drop down box selection. One selection is the query parameter value for the next drop down list.
Skip,
for a NUance!
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
Not sure what you actually want the final result to be. For instance, we could return a DISTINCT list of CODEs to a drop down and then the result of a selection of the second drop down, a list of Location(s) for that CODE selection is a possibility.
So 1) need a response on this result list and 2) direction if further refinement is required.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
Here are the results anyway:
Spreadsheet attached.
Column Q combines the ID code and the value, so you can do an exact Match on that to find the row number with the maximum value for the selected ID.
The formula in column Q is =J2:J10244&L2:L10244, entered as an array formula (select the range Q2:Q10244 then press Ctrl-Shift-Enter), for compatibility with older Excel versions. Alternatives are:
- Enter =J2&L2 in cell Q2 and copy down as far as required (also works in older versions)
- Type =J2:J10244&L2:L10244 in cell Q2 and press enter. In latest Excel versions this will generate a dynamic array, filling the cells down to row 10244 automatically.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value
Skip,
Just traded in my OLD subtlety...
for a NUance!