EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value 1

ENGINEER92 · Jun 2, 2020

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.

ENGINEER92 · Jun 2, 2020

The excel attached.

3DDave · Jun 2, 2020

Why would you not use INDEX()?

SkipVought · Jun 2, 2020

Hi,

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...
[pre]
Location REACTIONS [highlight #FCE94F]CODE[/highlight] 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
[/pre]

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,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought · Jun 2, 2020

If you just want a list of locations, just insert a QueryTable via Data > Get External Data > From Other Sources > Excel*... and drill down to your workbook. This approach would work better if the source table were on a separate sheet. I'll upload a sample shortly.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought · Jun 2, 2020

Take a look at this modified workbook and renamed because it needs to be a macro-enabled workbook. The macro renames the path of the query to the path of your workbook.

The Table is on the Data sheet and it has been made a Structured Table.

ENABLE MACROS to prepare the file.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

IDS · Jun 2, 2020

I'll be interested to see skips suggestion.

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/

SkipVought · Jun 2, 2020

It's there, Doug.

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,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought · Jun 3, 2020

I probably should have done this originally. I'm returning Location, CODE, Y, ordered by CODE, Location.

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!}

Denial · Jun 4, 2020

Can anyone explain why the OP's approach behaves so strangely?[ ] (It happens on my Excel 2010 as well as his Excel 2013.)

IDS · Jun 4, 2020

Denial - what is strange? It finds the first row with an exact match for the maximum value. What would you expect?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

Denial · Jun 4, 2020

Thanks, Doug.[ ] What I would expect is that I wouldn't have got my thinking into such a knot.[ ] Your comment encouraged me to take another look, and all is now clear.

SkipVought · Aug 20, 2020

ENGINEER92, are you there?

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

IDS · Aug 20, 2020

D'oh - I should read the whole thread before working on a solution. I just did what I suggested back in June.

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/

SkipVought · Aug 21, 2020

@Doug, I usually try to avoid using helper columns. But that often raises other issues for me like enabling macros et al., especially when there might be corporate restrictions. So I really like the simplicity of your solution. I'll be more open to considering that option in the future.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

EXCEL 2013: Lookup location of Maximum value with if criteria and Two values has same Maximum Value 1

ENGINEER92

Civil/Environmental

ENGINEER92

Civil/Environmental

3DDave

Aerospace

SkipVought

Aerospace

SkipVought

Aerospace

SkipVought

Aerospace

IDS

Civil/Environmental

SkipVought

Aerospace

SkipVought

Aerospace

Denial

Structural

IDS

Civil/Environmental

Denial

Structural

SkipVought

Aerospace

IDS

Civil/Environmental

SkipVought

Aerospace

Similar threads

Part and Inventory Search

Sponsor