Return two values from a separate list

[gamul]

Hi - I am trying to create a sheet for my company football pool (sorry if the topic is silly).

For each pool member they have their own sheet and each personal sheet has a list of teams and values.
The team list in column B is each NFL Team. The value list in column C is a data validation drop down with selections like: Available, Won, Loss, Current Selection and Pending Results.

On the sheet titled "Rankings", I have columns for win/loss results. I am looking to populate one or two cells with the current weeks selections. Each week a person takes two teams so at any given time there should be two teams that have a matching value of Current Selection.

I was able to work with VLookup to find the first match of "Current Selection" and return the right team. But I want to find both the teams that are marked as current selection and return both. The results can be displayed in one cell or individual cells.

Any suggestions around this? I would prefer to not use VBA as I am not sure all the pool members can enable the Dev tab. It is certainly an option but was hoping to be able to do this within the main excel worksheet.

Thanks,
Glenn

 

[Scott T]

Re: How to return two values from a separate list

[TABLE="class: grid, width: 433"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1st team
[/TD]
[TD]snd team
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]team 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]team 7
[/TD]
[TD]team 17
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]team 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]team 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]team 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]team 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]team 6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]team 7
[/TD]
[TD]current selection
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]team 8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]team 9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD]team 10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD]team 11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD]team 12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]team 13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD]team 14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD]team 15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][/TD]
[TD]team 16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][/TD]
[TD]team 17
[/TD]
[TD]current selection
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD]team 18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD]team 19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD][/TD]
[TD]team 20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD][/TD]
[TD]team 21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD][/TD]
[TD]team 22
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD]team 23
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In F2 for the current selection

=INDEX($B$2:$B$24,MATCH("Current Selection",$C$2:$c$24,0))

In G2 for the second current selection
This is an array function and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=INDEX($B$2:$B$24,LARGE(IF($C$2:$C$24="Current Selection",ROW($C$2:$C$24)-ROW($C$2)+1),1))

Change ranges to match your data

 

[gamul]

Re: How to return two values from a separate list

Yup - that works. Thanks a ton!