Index and Match for unique data

[wboney]

I have a workbook that has two sheets. On one sheet (Entries- SEC) is a list of people and events they are participating in as indicated with an "x" on their row under the event titles. On the second sheet (Bareback-SEC), I have a column for MRCA # that I need to fill with each participant who has an "x" under the column labeled BB on the Entries- SEC sheet. I believe that a Index and Match formula should work, but I think it also needs something to bring back all of the participants. As written currently, it only brings back the first instance. This is my first time trying to build using the Index and Match functions.

Formula Issue.xlsx
	A	B
1	MRCA #	Contestant Name
2	3	Wayne
3	3	=VLOOKUP(A3,'Entries- SEC'!A:B,2,0)
4	3	Wayne
5	3	Wayne
6	3	Wayne
7	3	Wayne
8	3	Wayne
9	3	Wayne
10	3	Wayne
11	3	Wayne
12	3	Wayne
13	3	Wayne
14	3	Wayne
15	3	Wayne
16	3	Wayne
17	3	Wayne
18	3	Wayne
19	3	Wayne
20	3	Wayne
21	3	Wayne
Bareback-SEC

Cell Formulas
Range	Formula
B2,B4:B21	B2	=VLOOKUP(A2,'Entries- SEC'!A:B,2,0)
A2:A21	A2	=INDEX('Entries- SEC'!$A$4:$A$27,MATCH("x",'Entries- SEC'!$G$4:$G$27,0))

Named Ranges
Name	Refers To	Cells
'Entries- SEC'!_FilterDatabase	='Entries- SEC'!$A$3:$U$226	B4:B21, B2

Formula Issue.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2							Event Entered
3	Member #	First Name	Last Name	Branch	Status	Date of Entry	BB	SB	BR	CD	SW	TDR	GBR	BRA	TRHD	TRHE
4	3	Wayne				####	x			x		x		x
5	270	Chris				####			x	x
6	287	Teegan Guy				####			x
7	370	Joseph				####	x		x	x	x
8	389	Dan				####				x
9	364	Caleb				####	x		x
10	271	Kendall regan				####							x
11	1	Adam				####	x							x		x
12	356	Troy				####			x
13	302	Cedric				####	x		x
14	300	Troy				####			x
15	387	David				####	x		x
16	310	Cody				####	x		x
17	375	Harrison				####	x			x
18	249	Jerrod				####						x			x
19	207	Greg				####										x
20	223	Diana				####				x				x
21	366	Lonnie				####			x
22	394	Henry "Hank"				####	x		x
23	391	Jonathon				####				x		x		x
24	395	Chris				####			x
25	396	Robert				####			x
26	390	Scott				####									x
27	384	Chad				####			x
Entries- SEC

 

[Anthony47]

Not sure I got your point, but if you need somewhere the numbers and the names of those that pertecipated to the BB event then use the formula

=FILTER('Entries- SEC'!A2:B100,'Entries- SEC'!G2:G100="x")

With the published data, the formula will return 2 columns by 9 rows:

  3 Wayne
370 Joseph
364 Caleb
  1 Adam
302 Cedric
387 David
310 Cody
375 Harrison
394 Henry "Hank"

This uses Office 365 FILTER function, FILTER function

 

[wboney]

Not sure I got your point, but if you need somewhere the numbers and the names of those that pertecipated to the BB event then use the formula

=FILTER('Entries- SEC'!A2:B100,'Entries- SEC'!G2:G100="x")

With the published data, the formula will return 2 columns by 9 rows:

  3 Wayne
370 Joseph
364 Caleb
  1 Adam
302 Cedric
387 David
310 Cody
375 Harrison
394 Henry "Hank"

This uses Office 365 FILTER function, FILTER function

When I try that formula I get a #SPILL! error. Also, All I need to fill on the sheet is the Column A MRCA #. I can get the actual names to come in from other formulas.

 

[Anthony47]

#SPILL means that there is not enough room to insert all the information; try the formula in an empty area to see what it returns
Is MRCA# the info in Entries - Sec!Column A?

 

[wboney]

When I try that formula I get a #SPILL! error. Also, All I need to fill on the sheet is the Column A MRCA #. I can get the actual names to come in from other formulas.

I can fix the #SPILL! error by putting an @ in front of the formula. But then I'm back to getting just the first instance of the result. In this case, the value 3. I changed the array in your formula to only retrieve column A since I only need the membership #.

Formula Issue.xlsx
	A	B
1	MRCA #	Contestant Name
2	3
3	3
4	3
5	3
6	3
7	3
8	3
9	3
10	3
11	3
12	3
13	3
14	3
15	3
16	3
17	3
18	3
19	3
20	3
21	3
Bareback-SEC

Cell Formulas
Range	Formula
A2:A21	A2	=@FILTER('Entries- SEC'!$A$2:$A$100,'Entries- SEC'!$G$2:$G$100="x")

Named Ranges
Name	Refers To	Cells
'Entries- SEC'!_FilterDatabase	='Entries- SEC'!$A$3:$U$226	A2:A21

 

[Anthony47]

The server was unavailable yesterday evening…
My original formula returns a "dynamic array", ie an array whose size depends on how many lines have to be reported. If some of the area needed are not free then you will get the #SPILL! Error. I guess all the cells now are filled with your "old" formulas, so clearing them should be enough.
The @ modify the meaning of the formula to get only the first row.

Again, to see what the formula tryes to return, insert in in a free area so that the formula can "spill" all its results

If you cannot guarantee that there will be enough free rows in the column where you set the formula, you could add INDEX to get only a limited number of rows.
For example:

=INDEX(FILTER('Entries- SEC'!A2:A100,'Entries- SEC'!$G$2:$G$100="x"),SEQUENCE(4),0)

This will return 4 lines, as defined by SEQUENCE(4); so modify that 4 to your number of available rows.

However, to prevent that you get a #REF! error in case that the returned array is shorter that your fixed limit, it's is better to envelop the formula with IFERROR:

=IFERROR(INDEX(FILTER('Entries- SEC'!A2:A100,'Entries- SEC'!$G$2:$G$100="x"),SEQUENCE(14),0),"")

With this formula you set a limit to 14 lines; in case the returned array has less the 14 lines the extra lines will be empty

To make sure that this result is coherent with the "contestant names", I suggest that you use the same formula to extract also those value; it will be sufficient that you copy the formula (the first line) to the next column and it will return datas from Entries- SEC'!B2:B100 (this is the Contestant Name, is it?)

 

[wboney]

The server was unavailable yesterday evening…
My original formula returns a "dynamic array", ie an array whose size depends on how many lines have to be reported. If some of the area needed are not free then you will get the #SPILL! Error. I guess all the cells now are filled with your "old" formulas, so clearing them should be enough.
The @ modify the meaning of the formula to get only the first row.

Again, to see what the formula tryes to return, insert in in a free area so that the formula can "spill" all its results

If you cannot guarantee that there will be enough free rows in the column where you set the formula, you could add INDEX to get only a limited number of rows.
For example:

=INDEX(FILTER('Entries- SEC'!A2:A100,'Entries- SEC'!$G$2:$G$100="x"),SEQUENCE(4),0)

This will return 4 lines, as defined by SEQUENCE(4); so modify that 4 to your number of available rows.

However, to prevent that you get a #REF! error in case that the returned array is shorter that your fixed limit, it's is better to envelop the formula with IFERROR:

=IFERROR(INDEX(FILTER('Entries- SEC'!A2:A100,'Entries- SEC'!$G$2:$G$100="x"),SEQUENCE(14),0),"")

With this formula you set a limit to 14 lines; in case the returned array has less the 14 lines the extra lines will be empty

To make sure that this result is coherent with the "contestant names", I suggest that you use the same formula to extract also those value; it will be sufficient that you copy the formula (the first line) to the next column and it will return datas from Entries- SEC'!B2:B100 (this is the Contestant Name, is it?)

Thanks Anthony. This worked. It also helped me to understand the dynamic formulas a little better.

 

[Anthony47]

Thank you for the feedback.
Office 365 has a lot of important features: dynamic arrays, Filter, Sequence (that we used in this problem), Lambda, Let (that we used in this other discussion: How can I Capitalize each word and remove all special characters and then combine all words in Excel?) and much much more...