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

Hello,

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!!
Temp.xlsx
Example.JPG
PPLUSEEAsked:
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
26975493v2.xlsx
0
 
barry houdiniCommented:
The formula

=LOOKUP(C17,C2:C7,N2:N7)

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

=INDEX(N2:N7,MATCH(C17,C2:C7,0))

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

=COUNTIF(C2:C7,MAX(C2:C7))

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

regards, barry
0
 
barry houdiniCommented:
This formula in B19 will give you the winning bidder based on the earliest bid when there's a tie

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

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


26975493.xlsx
0
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....

barry
0
 
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!
0
 
PPLUSEEAuthor Commented:
This was EXACTLY what I was needing. Thanks!!
0
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.