Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
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? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
Did you enter the formula as an array?
**Confirm formula using Ctrl+Shift+Enter, not just Enter. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
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. Valko" wrote: Did you enter the formula as an array? **Confirm formula using Ctrl+Shift+Enter, not just Enter. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
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. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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. Valko" wrote: Did you enter the formula as an array? **Confirm formula using Ctrl+Shift+Enter, not just Enter. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
This works I just didn't hit ctrl shift enter :)
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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. Valko" wrote: Did you enter the formula as an array? **Confirm formula using Ctrl+Shift+Enter, not just Enter. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranked Lookup
Good deal!
-- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... This works I just didn't hit ctrl shift enter :) "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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. Valko" wrote: Did you enter the formula as an array? **Confirm formula using Ctrl+Shift+Enter, not just Enter. -- Biff Microsoft Excel MVP "Newbie and Lost" wrote in message ... 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? "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Newbie and Lost" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero Value Ranked | Excel Worksheet Functions | |||
RANK Function - Zero Value Ranked as 1 - Should be 10 | Excel Worksheet Functions | |||
How to create a ranked list | Excel Worksheet Functions | |||
Ranked list | Excel Worksheet Functions | |||
Automatic updates of a ranked table | Excel Discussion (Misc queries) |