Formula to pull highest possible value from a table

[wallstreetwiz]

Hello,

I want to pull a value from one table into another, but it cannot be a value that is listed in a specific list (which continually grows). I have a blank table which I want to automatically populate with a value from a database that is sorted in descending order. The formula needs to pull the highest value, but if the same value is listed in a separate list, it needs to then take the next highest available value. Any guidance on this would be extremely helpful. Thank you in advance.

Thanks

 

[Zyg D]

Hi.

I think I got the question. He has these lines:

Manning
Brees
Stafford
Rodgers
Brady
Luck

They are sorted according some criteria (total points, which we do not see).

He wants to create another column of names.
Since Manning is in another table ("Drafted players"), he does not want this name to appear. It applies to every other name included in the "Drafted players" table.
In this case, our friend wallstreetwiz wants to have formulas to create this table (the same sorted manner):

Brees
Stafford
Rodgers
Brady
Luck

 

[chrispsn]

If you want to take the highest-ranked player from a list of players not chosen already (assuming your players are already sorted in descending order based on some score, higher being better):

=INDEX(rankings[Name],
SMALL(
IF(ISERROR(MATCH(rankings[Name], drafted[Name], 0)), rankings[Number], "")
, 1)
)

Enter as array formula (Ctrl-Shift-Enter instead of Enter).

For readability I set up two Excel tables:

- rankings has two columns: The Name of each player, and the record Number in that Excel Table.
- drafted has one column, being the Names of players already drafted.

The filter step is performed using everything inside IF. It generates an array containing row numbers of names that made it through the filter. Names that were caught by the filter appear as empty strings.

The selection step is performed by using SMALL to get the first (really the lowest) number in the array of filtered results. We then get the name associated with that row number using INDEX.

 

[Zyg D]

crispsn, Your logic is brilliant

However, the formula did not work for me. After some adjustments, this is the formula, which worked for me (2 MATCH'es and LARGE, because higher score is better):
=INDEX(rankings[Name];MATCH(LARGE(IF(ISERROR(MATCH(rankings[Name];drafted[Name];0));rankings[Number];"");1);rankings[Number];0))

The underlined number must be changed in order to show other players except the first in the list.