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


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!!
Who is Participating?
barry houdiniConnect With a Mentor Commented:
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

barry houdiniCommented:
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
barry houdiniCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

barry houdiniCommented:
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....

PPLUSEEAuthor Commented:
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!
PPLUSEEAuthor Commented:
This was EXACTLY what I was needing. Thanks!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.