×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

Why would you not use INDEX()?

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

Hi,

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,

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

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!

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

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!

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

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/

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

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!

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

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!

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

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

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

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/

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

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.

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

ENGINEER92, are you there?

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

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:

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

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!