Multiple Fields pulling into a summary form

%3CLINGO-SUB%20id%3D%22lingo-sub-1386411%22%20slang%3D%22en-US%22%3EMultiple%20Fields%20pulling%20into%20a%20summary%20form%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386411%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20some%20sample%20data.%20I%20am%20trying%20to%20take%20this%20spreadsheet%20and%20pull%20the%20player%20name%20and%20salary%20to%20a%20summary%20form%20for%20that%20team.%20Example%2C%20Lewisberry%20chose%20Hank%20Aaron%20in%20rd%201%20and%20Bill%20Castro%20in%20rd%202.%20I%20want%20the%20data%20to%20populate%20on%20a%20summary%20page%20for%20Lewisberry%20that%20shows%20their%20players%20and%20the%20salaries%3C%2FP%3E%3CP%3EI%20think%20i%20attached%20sample%2C%20but%20if%20for%20some%20reason%20you%20cantsee%20it%2C%20the%20data%20is%20like%20this%3A%3C%2FP%3E%3CP%3EPICK%20%23%20FRANCHISE%20Player%20Name%20SALARY%3CBR%20%2F%3E1%20Lewisberry%20AARON%2C%20Hank%20%2420.20%3CBR%20%2F%3E2%20Jack%20Street%20AASE%2C%20Don%20%242.40%3CBR%20%2F%3E3%20Middle%20Channel%20ABBOTT%2C%20Jim%20%244.90%3CBR%20%2F%3E4%20Menomonee%20ABERNATHY%2C%20Ted%20%249.00%3CBR%20%2F%3E5%20Cincinnati%20AKER%2C%20Jack%20%241.60%3CBR%20%2F%3E6%20Las%20Vegas%20ALEXANDER%2C%20Pete%20(Grover%20Cleveland)%20%2419.00%3CBR%20%2F%3E7%20Coastline%20ALBERS%2C%20Matt%20%241.00%3CBR%20%2F%3E8%20Fantomz%20BECK%2C%20Rod%20%242.90%3CBR%20%2F%3E9%20Madison%20East%20ADAMS%2C%20Babe%20%2415.90%3CBR%20%2F%3E10%20New%20Westminster%20BETANCOURT%2C%20Rafael%20%243.30%3CBR%20%2F%3E11%20Hollywood%20YOUNG%2C%20Cy%20%2416.10%3CBR%20%2F%3E12%20Kansas%20City%20A's%20TROUT%2C%20Dizzy%20%2412.60%3CBR%20%2F%3E13%20Purry%20SANTANA%2C%20Johan%20%2415.10%3CBR%20%2F%3E14%20Clockwork%20ADCOCK%2C%20Joe%20%245.10%3CBR%20%2F%3E15%20Iowa%20AFFELDT%2C%20Jeremy%20%242.20%3CBR%20%2F%3E16%20Wainwright%20AGEE%2C%20Tommie%20%241.20%3CBR%20%2F%3E17%20Nags%20Head%20AGUILERA%2C%20Rick%20%246.10%3CBR%20%2F%3E18%20Climax%20AGUIRRE%2C%20Hank%20%245.40%3CBR%20%2F%3E19%20Indiana%20AIKENS%2C%20Willie%20%240.50%3CBR%20%2F%3E20%20Detroit%20ALEXANDER%2C%20Doyle%20%2410.60%3CBR%20%2F%3E21%20San%20Tan%20Valley%20ALEXANDER%2C%20Dale%20%240.50%3CBR%20%2F%3E22%20Hoboken%20ALFONZO%2C%20Edgardo%20%245.30%3CBR%20%2F%3E23%20Chicago%20ALLEN%2C%20Cody%20%241.10%3CBR%20%2F%3E24%20Kansas%20City%20Blues%20ALLEN%2C%20****%20%2418.20%3CBR%20%2F%3E25%20Wrigleyville%20ALLEN%2C%20Johnny%20%2411.00%3CBR%20%2F%3E26%20Pigtown%20BAKER%2C%20Homerun%20%2415.50%3CBR%20%2F%3E27%20West%20Michigan%20BALFOUR%2C%20Grant%20%242.40%3CBR%20%2F%3E28%20Boston%20BALL%2C%20Diamond%20(Walter)%20%243.20%3CBR%20%2F%3E29%20Fort%20Worth%20BANCROFT%2C%20Dave%20%248.30%3CBR%20%2F%3E30%20Virginia%20BANDO%2C%20Sal%20%249.70%3CBR%20%2F%3E31%20Fire%20Lake%20BANKHEAD%2C%20Sam%20%241.10%3CBR%20%2F%3E32%20Motown%20BANKS%2C%20Ernie%20%2410.60%3CBR%20%2F%3EPICK%20%23%20FRANCHISE%20Player%20Name%20SALARY%3CBR%20%2F%3E33%20Motown%20BARFIELD%2C%20Jesse%20%244.20%3CBR%20%2F%3E34%20Fire%20Lake%20BARNES%2C%20Jesse%20%2410.50%3CBR%20%2F%3E35%20Virginia%20BARNHILL%2C%20Impo%20(David)%20%240.50%3CBR%20%2F%3E36%20Fort%20Worth%20BARRETT%2C%20Jimmy%20%240.90%3CBR%20%2F%3E37%20Boston%20BARTELL%2C%20****%20%246.30%3CBR%20%2F%3E38%20West%20Michigan%20BASSLER%2C%20Johnny%20%240.50%3CBR%20%2F%3E39%20Pigtown%20BATES%2C%20Johnny%20%247.40%3CBR%20%2F%3E40%20Wrigleyville%20BATTEY%2C%20Earl%20%241.20%3CBR%20%2F%3E41%20Kansas%20City%20Blues%20BAUER%2C%20Hank%20%244.80%3CBR%20%2F%3E42%20Chicago%20BAY%2C%20Jason%20%245.10%3CBR%20%2F%3E43%20Hoboken%20BAUTISTA%2C%20Jose%20%249.10%3CBR%20%2F%3E44%20San%20Tan%20Valley%20BAYLOR%2C%20Don%20%247.30%3CBR%20%2F%3E45%20Detroit%20BEAUMONT%2C%20Ginger%20%2410.00%3CBR%20%2F%3E46%20Indiana%20BECK%2C%20Rod%20%242.90%3CBR%20%2F%3E47%20Climax%20BECKER%2C%20Beals%20%240.50%3CBR%20%2F%3E48%20Nags%20Head%20BECKERT%2C%20Glenn%20%241.90%3CBR%20%2F%3E49%20Wainwright%20BECKETT%2C%20Josh%20%2411.60%3CBR%20%2F%3E50%20Iowa%20BECKLEY%2C%20Jake%20%243.10%3CBR%20%2F%3E51%20Clockwork%20BECKWITH%2C%20John%20%249.90%3CBR%20%2F%3E52%20Purry%20BEDROSIAN%2C%20Steve%20%245.30%3CBR%20%2F%3E53%20Kansas%20City%20A's%20BEGGS%2C%20Joe%20%241.90%3CBR%20%2F%3E54%20Hollywood%20BELANGER%2C%20Mark%20%240.50%3CBR%20%2F%3E55%20New%20Westminster%20BELL%2C%20Buddy%20%246.60%3CBR%20%2F%3E56%20Madison%20East%20BELL%2C%20Cool%20Papa%20(James)%20%2410.00%3CBR%20%2F%3E57%20Fantomz%20BELL%2C%20George%20%245.00%3CBR%20%2F%3E58%20Coastline%20BELL%2C%20Gus%20%245.40%3CBR%20%2F%3E59%20Las%20Vegas%20BELL%2C%20Heath%20%241.10%3CBR%20%2F%3E60%20Cincinnati%20BELL%2C%20William%20%241.10%3CBR%20%2F%3E61%20Menomonee%20BELLE%2C%20Albert%20%2411.20%3CBR%20%2F%3E62%20Middle%20Channel%20BELT%2C%20Brandon%20%243.40%3CBR%20%2F%3E63%20Jack%20Street%20GAGNE%2C%20Eric%20%244.90%3CBR%20%2F%3E64%20Lewisberry%20CASTRO%2C%20Bill%20%240.50%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1386411%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388336%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Fields%20pulling%20into%20a%20summary%20form%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388336%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666346%22%20target%3D%22_blank%22%3E%40scutler3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20simple%20create%20a%20Pivot%20Table%20as%20all%20your%20data%20is%20already%20separately%20columned.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20data.%20I%20think%20this%20is%20what%20you%20are%20asking%20for.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388999%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Fields%20pulling%20into%20a%20summary%20form%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666346%22%20target%3D%22_blank%22%3E%40scutler3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20possible%20solution%20for%20your%20query%3C%2FP%3E%3CP%3EGives%20you%20the%20CORRECT%20results%20in%20all%20cases%20EXCEPT%20Lewisberry%20(may%20be%20due%20to%20First%20Row)%3C%2FP%3E%3CP%3ECan't%20seem%20to%20figure%20a%20way%20out%20of%20it%20SORRY!%3C%2FP%3E%3CP%3EHopefully%20someone%20else%20can%20point%20out%20the%20mistake%20I%20made%3C%2FP%3E%3CP%3EAnyways%20cheers%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1389146%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Fields%20pulling%20into%20a%20summary%20form%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1389146%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F666346%22%20target%3D%22_blank%22%3E%40scutler3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20achieve%20the%20result%20using%26nbsp%3B%3CSPAN%3EINDEX%2C%20SMALL%20%26amp%3B%20ROW%20functions.%20See%20attached%20worksheet.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20formula%20I%20used%20was%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%7B%3DIFERROR(INDEX(Data%2C%20SMALL(IF(%24D%241%3DData%5BFRANCHISE%5D%2C%20ROW(Data%5BFRANCHISE%5D)-ROW(Data%5B%5B%23Headers%5D%2C%5BFRANCHISE%5D%5D))%2C%20ROW(1%3A1))%2C3)%2C%22%22%20)%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20entered%20as%20an%20array%20using%26nbsp%3B%3CSPAN%3Epressing%20Ctrl%2BShift%2BEnter%20for%20non-office%20365%20users.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20formated%20the%20information%20on%20tab%20%22draft%20order%22%20as%20a%20table%20named%20%22Data%22%2C%20in%20case%20you%20are%20wondering%20where%20Data%20is%20coming%20from%20in%20the%20formula%20and%20the%20formula%20used%20table%20structured%20referencing.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20also%20used%20data%20validation%20for%20the%20team%20selection%20on%20cell%20D1%20in%20tab%20%22Roster%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHopefully%2C%20this%20is%20what%20you%20are%20looking%20for.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECheers.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Here is some sample data. I am trying to take this spreadsheet and pull the player name and salary to a summary form for that team. Example, Lewisberry chose Hank Aaron in rd 1 and Bill Castro in rd 2. I want the data to populate on a summary page for Lewisberry that shows their players and the salaries

I think i attached sample, but if for some reason you cantsee it, the data is like this:

PICK # FRANCHISE Player Name SALARY
1 Lewisberry AARON, Hank $20.20
2 Jack Street AASE, Don $2.40
3 Middle Channel ABBOTT, Jim $4.90
4 Menomonee ABERNATHY, Ted $9.00
5 Cincinnati AKER, Jack $1.60
6 Las Vegas ALEXANDER, Pete (Grover Cleveland) $19.00
7 Coastline ALBERS, Matt $1.00
8 Fantomz BECK, Rod $2.90
9 Madison East ADAMS, Babe $15.90
10 New Westminster BETANCOURT, Rafael $3.30
11 Hollywood YOUNG, Cy $16.10
12 Kansas City A's TROUT, Dizzy $12.60
13 Purry SANTANA, Johan $15.10
14 Clockwork ADCOCK, Joe $5.10
15 Iowa AFFELDT, Jeremy $2.20
16 Wainwright AGEE, Tommie $1.20
17 Nags Head AGUILERA, Rick $6.10
18 Climax AGUIRRE, Hank $5.40
19 Indiana AIKENS, Willie $0.50
20 Detroit ALEXANDER, Doyle $10.60
21 San Tan Valley ALEXANDER, Dale $0.50
22 Hoboken ALFONZO, Edgardo $5.30
23 Chicago ALLEN, Cody $1.10
24 Kansas City Blues ALLEN, **** $18.20
25 Wrigleyville ALLEN, Johnny $11.00
26 Pigtown BAKER, Homerun $15.50
27 West Michigan BALFOUR, Grant $2.40
28 Boston BALL, Diamond (Walter) $3.20
29 Fort Worth BANCROFT, Dave $8.30
30 Virginia BANDO, Sal $9.70
31 Fire Lake BANKHEAD, Sam $1.10
32 Motown BANKS, Ernie $10.60
PICK # FRANCHISE Player Name SALARY
33 Motown BARFIELD, Jesse $4.20
34 Fire Lake BARNES, Jesse $10.50
35 Virginia BARNHILL, Impo (David) $0.50
36 Fort Worth BARRETT, Jimmy $0.90
37 Boston BARTELL, **** $6.30
38 West Michigan BASSLER, Johnny $0.50
39 Pigtown BATES, Johnny $7.40
40 Wrigleyville BATTEY, Earl $1.20
41 Kansas City Blues BAUER, Hank $4.80
42 Chicago BAY, Jason $5.10
43 Hoboken BAUTISTA, Jose $9.10
44 San Tan Valley BAYLOR, Don $7.30
45 Detroit BEAUMONT, Ginger $10.00
46 Indiana BECK, Rod $2.90
47 Climax BECKER, Beals $0.50
48 Nags Head BECKERT, Glenn $1.90
49 Wainwright BECKETT, Josh $11.60
50 Iowa BECKLEY, Jake $3.10
51 Clockwork BECKWITH, John $9.90
52 Purry BEDROSIAN, Steve $5.30
53 Kansas City A's BEGGS, Joe $1.90
54 Hollywood BELANGER, Mark $0.50
55 New Westminster BELL, Buddy $6.60
56 Madison East BELL, Cool Papa (James) $10.00
57 Fantomz BELL, George $5.00
58 Coastline BELL, Gus $5.40
59 Las Vegas BELL, Heath $1.10
60 Cincinnati BELL, William $1.10
61 Menomonee BELLE, Albert $11.20
62 Middle Channel BELT, Brandon $3.40
63 Jack Street GAGNE, Eric $4.90
64 Lewisberry CASTRO, Bill $0.50

3 Replies
Highlighted

Hello @scutler3 

 

You can simple create a Pivot Table as all your data is already separately columned. 

 

Please see the attached data. I think this is what you are asking for. 

Highlighted

@scutler3 

Hey !

 

I have attached a possible solution for your query

Gives you the CORRECT results in all cases EXCEPT Lewisberry (may be due to First Row)

Can't seem to figure a way out of it SORRY!

Hopefully someone else can point out the mistake I made

Anyways cheers 

Highlighted

Hi @scutler3 

 

You can achieve the result using INDEX, SMALL & ROW functions. See attached worksheet.

 

The formula I used was 

 

{=IFERROR(INDEX(Data, SMALL(IF($D$1=Data[FRANCHISE], ROW(Data[FRANCHISE])-ROW(Data[[#Headers],[FRANCHISE]])), ROW(1:1)),3),"" )}

 

It was entered as an array using pressing Ctrl+Shift+Enter for non-office 365 users.

 

I formated the information on tab "draft order" as a table named "Data", in case you are wondering where Data is coming from in the formula and the formula used table structured referencing.

 

I have also used data validation for the team selection on cell D1 in tab "Roster"

 

Hopefully, this is what you are looking for.

 

Cheers.