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

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

# 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
• 4
• 2
1 Solution

Commented:
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

Commented:
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

Commented:
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

Author 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

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

Author Commented:
This was EXACTLY what I was needing. Thanks!!
0

## Featured Post

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