Ranked Lookup

N

Newbie and Lost

I am trying to find a function that will help me with a side project I am
working on. Simple Example: I have a list of ranked employees 1 - 10. In
A2:b12 A= Rank B = Employee Name. There is a bid/draft for these employees.
I am 5th in line to pick. I wanted to create a formula that would allow me
to decide what pick to make depending on what employees were eliminated on
picks 1 thru 4 AND who is ranked the highest in A2:B12. Is this possible to
do? If so how?
 
L

Luke M

I'm assuming rank 1 is preferred choice (if not, change MIN to MAX). Also
assumes that you place a "x" in C2:C12 if an employee has already been picked.

Array** formula is:
=INDEX(B2:B12,MATCH(MIN(IF(C2:C12<>"x",A2:A12)),A2:A12,0))

**Confirm formula using Ctrl+Shift+Enter, not just Enter.
 
N

Newbie and Lost

That didn't seem to work. It just keeps returning the #1 value "Frank". Is
there anything that may just need to be ordered differently? maybe IF before
MIN?
 
N

Newbie and Lost

Yes and it still returns the top value whether or not there is an "x" next to
it. It should eliminate the top employee but it does not seem to do that.
 
T

T. Valko

With this data:

...........A..........B..........C
1.....Rank....Player...Drafted
2.......5.........Joe...................
3.......2.........Tom.................
4.......4.........Ed....................
5.......1.........Jim...................
6.......3.........Tim..................

and this array formula** entered in E2:

=INDEX(B2:B6,MATCH(MIN(IF(C2:C6<>"x",A2:A6)),A2:A6,0))

The formula correctly returns Jim.

If I place a "x" in C5 then the formula correctly returns Tom. If I place a
"x" in C3 then the formula correctly returns Tim.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
N

Newbie and Lost

This works I just didn't hit ctrl shift enter :)

T. Valko said:
With this data:

...........A..........B..........C
1.....Rank....Player...Drafted
2.......5.........Joe...................
3.......2.........Tom.................
4.......4.........Ed....................
5.......1.........Jim...................
6.......3.........Tim..................

and this array formula** entered in E2:

=INDEX(B2:B6,MATCH(MIN(IF(C2:C6<>"x",A2:A6)),A2:A6,0))

The formula correctly returns Jim.

If I place a "x" in C5 then the formula correctly returns Tom. If I place a
"x" in C3 then the formula correctly returns Tim.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top