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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PPLUSEEAuthor Commented:
This was EXACTLY what I was needing. Thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.