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

	A	B	C	D	E	F	G
1						1st team	snd team
2		team 1				team 7	team 17
3		team 2
4		team 3
5		team 4
6		team 5
7		team 6
8		team 7	current selection
9		team 8
10		team 9
11		team 10
12		team 11
13		team 12
14		team 13
15		team 14
16		team 15
17		team 16
18		team 17	current selection
19		team 18
20		team 19
21		team 20
22		team 21
23		team 22
24		team 23

<tbody>
</tbody>

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!