Return multiple fields based on single critera - also count of MAX within a range

Posted on 2011-04-25
Last Modified: 2012-05-11

I am working on a simple spreadsheet that captures bids of users for certain item. What I need to know is how to return first and last name fields based on another fields value. The criteria value would be the result of a MAX function on the particular row of bids, in other words, return me the first and last name of the highest bidder for that item. I would also like to know if there are more than one person with the same high bid. A plus would be to look at the date/time stamp in row O and place return the person's name who placed that high bid first, as the winning bidder. :) But that would be a plus. The first two items are a must. In the attached spreadsheet you will see I attemtped using a LOOKUP function, but it ONLY works if all the cells for a particular item have a non-zero value in it. I am sure this is easy for the Excel guru's, I'm just not that good with Excel. Thanks!!
Question by:PPLUSEE
    LVL 50

    Expert Comment

    by:barry houdini
    The formula


    won't work reliably because with LOOKUP the lookup range needs to be sorted ascending, which you can't guarantee a reliable formula to return the first name associated with MAX value will be


    To count the number of MAX bids you need COUNTIF, e.g.


    ....just looking at the rest of your question......

    regards, barry
    LVL 50

    Expert Comment

    by:barry houdini
    This formula in B19 will give you the winning bidder based on the earliest bid when there's a tie


    This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

    see attached

    regards, barry

    LVL 50

    Expert Comment

    by:barry houdini
    Sorry, should have said.....after that formula is entered in B19 and confirmed with CTRL+SHIFT+ENTER it can be copied across the whole row to L19 as per my example

    Ignore C14 - I meant to delete that....


    Author Comment

    You are the MAN! Thank you!!

    Just one other question, is it a big deal to return both first name and last? If so, it's no big deal, just would help out a little bit more. Thanks!
    LVL 50

    Accepted Solution

    You can use this version to return first and last names

    =INDEX($M2:$M7&" "&$N2:$N7,MATCH(1,(B2:B7=B17)*($O2:$O7+0=MIN(IF(B2:B7=B17,$O2:$O7+0))),0))

    see revised attachment


    Author Closing Comment

    This was EXACTLY what I was needing. Thanks!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now