VLOOKUP Formula and ranking

A

astrophys

New Member
Joined
Apr 21, 2017
Messages
14
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!

Sheet1
RANKNAMEJSJJSBAVG
2Evans1232.00
3Beckham3312.33
1Brown2121.67

<tbody>
</tbody>


Sheet2
RANKNAMEJSJJSBAVG
3Johnson1533.00
2Elliot3312.33
1Bell2121.67

<tbody>
</tbody>

FINAL
RANKNAMEJSJJSB
1Brown212
2Evans123
3Beckham331

<tbody>
</tbody>

SHEET1<o:p></o:p>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="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> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></v:path></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:p></o:p>
<o:p> </o:p>
SHEET2<o:p></o:p>
<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:p></o:p>
<o:p> </o:p>
FINAL<o:p></o:p>
<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:p></o:p>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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:

ABCDEFG
1RANKNAMEJSJJSBAverageSheet&Row
21Johnson15332002
32Elliot3312.332003
43Beckham3312.331003
54Evans12321002
65Bell2121.672004
76Brown2121.671004
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Final​

Worksheet Formulas
CellFormula
A2=IF(F2<>"",ROW()-1,"")
B2=IF($F2<>"",INDIRECT("Sheet"&INT($G2/1000)&"!"&ADDRESS(MOD($G2,1000),COLUMN())),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
F2{=IFERROR(1/(1/LARGE((Sheet1!$F$2:$F$6*{1,0}+Sheet2!$F$2:$F$6*{0,1}),ROWS($F$2:$F2))),"")}
G2{=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)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
With the WR sheet looking like:

ABCDEFG
1RANKNAMEJSJJSBAVG
22Evans1232
33Beckham3312.33
41Brown2121.67

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
WR​



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

ABCDEFGH
1
2RANKNAMEJSJJSBAverageSheet&Row
31Brown2121.671004
41Bell2121.672004
53Evans12321002
64Beckham3312.331003
74Elliot3312.332003
86Johnson15332002
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Final​

Worksheet Formulas
CellFormula
A3=IF(G3="","",IF(G3=G2,A2,ROW()-2))
B3=IF($G3<>"",INDIRECT(CHOOSE($H3/1000,"WR!","RB!")&TEXT(MOD($H3,1000)*10+COLUMN(),"R000C0"),0),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G3{=IFERROR(SMALL(IFERROR(1/(1/(WR!$G$2:$G$6*{1,0}+RB!$G$2:$G$6*{0,1})),""),ROWS($G$3:$G3)),"")}
H3{=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)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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!
 
Upvote 0
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!
 
Last edited:
Upvote 0
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! :cool:

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.
 
Last edited:
Upvote 0
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!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Find the row when a sum on a column with certain values on rows is >= of a value
Replies
7
Views
951
XOR LX
XOR LX
H
  • Question
Vba to change Excel file Properties like Title, Description, Fiscal, Period, file
Replies
0
Views
821
hemant_sonawane
H
S
  • Question
Correct Formulas for Mathematical Equations.
Replies
3
Views
738
siash69
S
M
  • Question
Counting between 2 worksheets where 1 is filtered.
Replies
0
Views
514
M4rek
M
R
  • Question
UserForm help
Replies
2
Views
703
rholdren
R

Forum statistics

Threads
1,214,827
Messages
6,121,803
Members
449,048
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top