[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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
0
PPLUSEE
Asked:
PPLUSEE
  • 4
  • 2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now