×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

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

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,

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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:


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

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close