VLOOKUP Formula and ranking

[astrophys]

I have a total of 3 worksheets. Two of the 3 consist of a rank from an average of 3 rankers (JS, JJ and SB). On the FINAL worksheet I'm trying to consolidate the information from the previous 2 worksheets and rank (from lowest to highest) from their averages. I have the formula on the FINAL worksheet working I have a total of 3 worksheets. Two of the 3 consist of a rank from an average of 3 rankers (JS, JJ and SB). On the FINAL worksheet I'm trying to consolidate the information from the previous 2 worksheets and rank (from lowest to highest) from their averages. I have the formula on the FINAL worksheet working for Sheet 1...now I just need to incorporate Sheet2 data where Bell is displayed between Brown and Evans on the FINAL worksheet. Attached are 3 images of each of my worksheets as I do not see an option to attach an excel file.

The formula in the RANK column on the FINAL worksheet is:
=IFERROR(SMALL(Sheet1!$A$3:$A$250,ROWS(FINAL!$A$3:$A3)),"")

For the REMANING columns is:
=IFERROR(VLOOKUP($A3,Sheet1!$A$3:$F$5,COLUMNS(FINAL!$B3:B3)+1,0),"")

As you can see Sheet1 is within the formula. How do I incorporate Sheet2?

Thank you in advance!

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RANK[/TD]
[TD]NAME[/TD]
[TD]JS[/TD]
[TD]JJ[/TD]
[TD]SB[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Evans[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Beckham[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2.33[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brown[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1.67[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RANK[/TD]
[TD]NAME[/TD]
[TD]JS[/TD]
[TD]JJ[/TD]
[TD]SB[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Johnson[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Elliot[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2.33[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bell[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1.67[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]FINAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RANK[/TD]
[TD]NAME[/TD]
[TD]JS[/TD]
[TD]JJ[/TD]
[TD]SB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brown[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Evans[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Beckham[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

SHEET1<o></o>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_1" style="width: 169.2pt; height: 115.8pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:borderrightcolor="yellow pure" o:borderbottomcolor="yellow pure" o:borderleftcolor="yellow pure" o:bordertopcolor="yellow pure" o:spid="_x0000_i1027"> <v:imagedata o:title="" cropright="40803f" cropbottom="34279f" src="file:///C:/Users/JNJOHN~1/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png"> <w:bordertop type="single" width="8"> <w:borderleft type="single" width="8"> <w:borderbottom type="single" width="8"> <w:borderright type="single" width="8"></w:borderright></w:borderbottom></w:borderleft></w:bordertop></v:imagedata></v:shape><o></o>
<o> </o>
SHEET2<o></o>
<v:shape id="Picture_x0020_2" style="width: 173.4pt; height: 130.2pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:borderrightcolor="yellow pure" o:borderbottomcolor="yellow pure" o:borderleftcolor="yellow pure" o:bordertopcolor="yellow pure" o:spid="_x0000_i1026"> <v:imagedata o:title="" cropright="41170f" cropbottom="31750f" src="file:///C:/Users/JNJOHN~1/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png"> <w:bordertop type="single" width="8"> <w:borderleft type="single" width="8"> <w:borderbottom type="single" width="8"> <w:borderright type="single" width="8"></w:borderright></w:borderbottom></w:borderleft></w:bordertop></v:imagedata></v:shape><o></o>
<o> </o>
FINAL<o></o>
<v:shape id="Picture_x0020_3" style="width: 280.8pt; height: 156pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:borderrightcolor="yellow pure" o:borderbottomcolor="yellow pure" o:borderleftcolor="yellow pure" o:bordertopcolor="yellow pure" o:spid="_x0000_i1025"> <v:imagedata o:title="" cropright="24114f" cropbottom="20384f" src="file:///C:/Users/JNJOHN~1/AppData/Local/Temp/msohtmlclip1/01/clip_image005.png" cropleft="2100f" croptop="4671f"> <w:bordertop type="single" width="8"> <w:borderleft type="single" width="8"> <w:borderbottom type="single" width="8"> <w:borderright type="single" width="8"></w:borderright></w:borderbottom></w:borderleft></w:bordertop></v:imagedata></v:shape><o></o>

 

[Eric W]

First of all, the formula in the RANK column on the final sheet appears to be a fancy way of creating 1,2,3, etc. In any event, I managed to create the list you want as follows:

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]RANK[/TD]
[TD="bgcolor: #FAFAFA"]NAME[/TD]
[TD="bgcolor: #FAFAFA"]JS[/TD]
[TD="bgcolor: #FAFAFA"]JJ[/TD]
[TD="bgcolor: #FAFAFA"]SB[/TD]
[TD="bgcolor: #FAFAFA"]Average[/TD]
[TD="bgcolor: #FAFAFA"]Sheet&Row[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Johnson[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2002[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA"]Elliot[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]2.33[/TD]
[TD="align: right"]2003[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA"]Beckham[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"]2.33[/TD]
[TD="align: right"]1003[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA"]Evans[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1002[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA"]Bell[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]2004[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA"]Brown[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]1004[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

</tbody>

Final​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=IF(F2<>"",ROW()-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF($F2<>"",INDIRECT("Sheet"&INT($G2/1000)&"!"&ADDRESS(MOD($G2,1000),COLUMN())),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=IFERROR(1/(1/LARGE((Sheet1!$F$2:$F$6*{1,0}+Sheet2!$F$2:$F$6*{0,1}),ROWS($F$2:$F2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=IF(F2="","",LARGE(IF(Sheet1!$F$2:$F$6=F2,ROW($F$2:$F$6)+1000)*{1,0}+IF(Sheet2!$F$2:$F$6=F2,ROW($F$2:$F$6)+2000)*{0,1},COUNTIF($F$2:$F2,F2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I added 2 helper columns in F and G. You could get rid of column G if you want and incorporate that formula into the B2 formula, but that would make it a monster. You'd do better to just hide column F and/or G if you prefer. In any event, the array formula in F finds the highest averages from both sheets and lists them in order. The array formula in G looks on both sheets and finds the matching row(s), and returns a number that is the sheet*1000 + the row. Then the formula in B2 (which you drag across to E, and down as far as needed) will get the matching values. The A formula is just a simple counter that ends when the list in F ends.

Let me know if this works for you.

 

[astrophys]

So, I added all of the formulas in the FINAL tab. I had to slightly modify them to suit my needs as your Column F was my G and your Column G was my H; however, I'm not getting the second sheet to transfer over to the FINAL tab. In addition, the Sheet&Row column isn't populating correctly. Is there a way to attach my example so you can see exactly what I'm trying to do? I recall using this forum a couple of months ago and it gave me the option to upload.

Thanks again for you help with this!

 

[Eric W]

Are your sheets actually named "Sheet1" and "Sheet2"? If not, that could be an issue. Is the layout on both Sheet1 and Sheet2 the same?

This forum doesn't allow attachments, and hasn't in the 2 years or so that I've been coming around. You can upload your workbook to a file sharing service and post the link, but many people here won't or can't go to external links. The best alternative is to use the HTML Maker, or one of the other similar tools, to post a screenshot of your workbooks. Look at the link in my signature for how to obtain and use it. My screenshot in post #2 came from that.

 

[astrophys]

So, my sheets names are WR and RB. Each sheet has the exact same layout (i.e. RANK is in Column A, Name is Column B, and so on). I'm getting a #VALUE! and #REF! error for Column B as I assume the "Sheet" reference is wrong since my sheets are named differently. I believe the RANK column is functioning correctly; however, the RB (aka Sheet2) information isn't populating over. For the AVG column, I changed "LARGE" to "SMALL" as I want it lowest to highest order. The same applies to the Sheet&Row column. Another question: would the same formula for the NAME column be used throughout (i.e. JS, JJ and SB columns)? I'm wanting that information to populate from the 2 pre-existing sheets (WR and RB) on the FINAL sheet.

So my current formulas for the FINAL worksheets are:
RANK: =IF(G3<>"",ROW()-2,"")
NAME: =IF($G3<>"",INDIRECT("Sheet"&INT($H3/1000)&"!"&ADDRESS(MOD($H3,1000),COLUMN())),"")
JS, JJ and SB: =IFERROR(VLOOKUP($A3,WR!$A$3:$F$5,COLUMNS(FINAL!$B3:D3)+1,0),"")
AVG: =IFERROR(1/(1/SMALL((WR!$G$3:$G$5*{1,0}+RB!$G$3:$G$5*{0,1}),ROWS($G$3:$G3))),"")
Sheet&Row: =IF(G3="","",SMALL(IF(WR!$G$3:$G$5=G3,ROW($G$3:$G$5)+1000)*{1,0}+IF(RB!$G$3:$G$5=G3,ROW($G$3:$G$5)+2000)*{0,1},COUNTIF($G$3:$G3,G3)))

 

[Eric W]

With the WR sheet looking like:

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]RANK[/TD]
[TD="bgcolor: #FAFAFA"]NAME[/TD]
[TD="bgcolor: #FAFAFA"]JS[/TD]
[TD="bgcolor: #FAFAFA"]JJ[/TD]
[TD="bgcolor: #FAFAFA"]SB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]AVG[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA"]Evans[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA"]Beckham[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2.33[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Brown[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1.67[/TD]

</tbody>

WR​

and the RB sheet the same layout, then the Final sheet can be:

	A	B	C	D	E	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]RANK[/TD]
[TD="bgcolor: #FAFAFA"]NAME[/TD]
[TD="bgcolor: #FAFAFA"]JS[/TD]
[TD="bgcolor: #FAFAFA"]JJ[/TD]
[TD="bgcolor: #FAFAFA"]SB[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Average[/TD]
[TD="bgcolor: #FAFAFA"]Sheet&Row[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Brown[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]1004[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]Bell[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]1.67[/TD]
[TD="align: right"]2004[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA"]Evans[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1002[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA"]Beckham[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]2.33[/TD]
[TD="align: right"]1003[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA"]Elliot[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]2.33[/TD]
[TD="align: right"]2003[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA"]Johnson[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2002[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Final​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=IF(G3="","",IF(G3=G2,A2,ROW()-2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF($G3<>"",INDIRECT(CHOOSE($H3/1000,"WR!","RB!")&TEXT(MOD($H3,1000)*10+COLUMN(),"R000C0"),0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]{=IFERROR(SMALL(IFERROR(1/(1/(WR!$G$2:$G$6*{1,0}+RB!$G$2:$G$6*{0,1})),""),ROWS($G$3:$G3)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]{=IF(G3="","",SMALL(IF(WR!$G$2:$G$6=G3,ROW(WR!$G$2:$G$6)+1000,9^9)*{1,0}+IF(RB!$G$2:$G$6=G3,ROW(RB!$G$2:$G$6)+2000,9^9)*{0,1},COUNTIF($G$3:$G3,G3)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Average on all 3 sheets is in column G. One header on WR and RB, 2 lines of headers on Final.

Some notes on the formulas:

Average: it was actually a bit easier to use LARGE instead of SMALL, because if you have empty rows in your range, they'll have an average of 0, and with LARGE they'll be the last ones to look at. If you just switch LARGE to SMALL, then they'll be the first ones to look at. So to eliminate any zero values, I used the IFERROR(1/(1/ trick to turn them into nulls, which SMALL will ignore.

Sheet&Row: I added the sheet references in 2 places, so that if you add/insert rows on WR or RB, the formula will still work. I also changed LARGE to SMALL so that in case of a tie, names from the first sheet (WR), or first on a given sheet, will show up first. This also required the 9^9 to be added.

Rank: Your existing formula works fine, but just to show you another option, the one here will show ties as the same rank. There are other options.

Name: Originally I just appended the first digit from the Sheet&Row column to Sheet to get the name of the sheet. Since your names are WR and RB, that didn't work. So I used the CHOOSE to pick the right sheet name. I also tweaked it a bit to use TEXT instead of ADDRESS to make it a bit shorter. Also, yes, use the same formula from NAME for the JS, JJ, and SB columns. This formula pulls the data from the same column that the formula is in. Since this formula is in column B, it gets the data from column B from the source sheets. If you copy the formula to column C, it will get data from column C from the source sheets. This behavior can be changed by changing the +COLUMN() part of the formula.

Let me know how this works!

 

[astrophys]

THANK YOU, Eric!! This works!! One last question (I hope), what if I wanted to add an additional worksheet named 'DT'? I assume the following formulas would then be:

NAME: =IF($G3<>"",INDIRECT(CHOOSE($H3/1000,"WR!","RB!","DT!")&TEXT(MOD($H3,1000)*10+COLUMN(),"R000C0"),0),"")
AVG: {=IFERROR(SMALL(IFERROR(1/(1/(WR!$G$2:$G$6*{1,0}+RB!$G$2:$G$6*{0,1}+DT!$G$2:$G$6*{0,1})),""),ROWS($G$3:$G3)),"")}
Sheet&Row: {=IF(G3="","",SMALL(IF(WR!$G$2:$G$6=G3,ROW(WR!$G$2:$G$6)+1000,9^9)*{1,0}+IF(RB!$G$2:$G$6=G3,ROW(RB!$G$2:$G$6)+2000,9^9+IF(DT!$G$2:$G$6=G3,ROW(DT!$G$2:$G$6)+3000,9^9)*{0,1},COUNTIF($G$3:$G3,G3)))}

Thanks again for all of your help!

 

[Eric W]

Close! The Name formula should be OK. The AVG formula should be:

=IFERROR(SMALL(IFERROR(1/(1/(WR!$G$2:$G$6*{1,0,0}+RB!$G$2:$G$6*{0,1,0}+DT!$G$2:$G$6*{0,0,1})),""),ROWS($G$3:$G3)),"")

and the Sheet&Row should be:

=IF(G3="","",SMALL(IF(WR!$G$2:$G$6=G3,ROW(WR!$G$2:$G$6)+1000,9^9)*{1,0,0}+IF(RB!$G$2:$G$6=G3,ROW(RB!$G$2:$G$6)+2000,9^9)*{0,1,0}+IF(DT!$G$2:$G$6=G3,ROW(DT!$G$2:$G$6)+3000,9^9)*{0,0,1},COUNTIF($G$3:$G3,G3)))

You pretty much figured out the part in blue. The red parts are array constants. The way both formulas work is they check each sheet for the values, and then puts those values in a different column of an array. The array constant says which column to put the values in. When you had just 2 sheets, you just need {1,0} and {0,1}. With 3 sheets, you need 3 columns.

Glad I could help!

One more thought: This will work for up to 999 rows. If your ranges are longer, you'll need to change the 1000 to 10000 everywhere, and the R000C0 to R0000C0 in the TEXT function. And not go over column J.

 

[astrophys]

You mentioned in the thread above for Column A using =IF(G3="","",IF(G3=G2,A2,ROW()-2)) will show a tie for the same rank (i.e. Rank 1 [Bell and Brown] and Rank 4 for both Beckham and Elliott). What would be the formula to rank them 1-5? I would prefer not to show a tie.

Thank you in advance!