Need help with Index Function

%3CLINGO-SUB%20id%3D%22lingo-sub-1412095%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20Index%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412095%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20created%20a%20file%20to%20pull%20a%20random%20list%20from%20column%20A%2C%20utilizing%20Rand()%20in%20column%20B%20and%20the%20following%20Index%20Function%20in%20Column%20C%3A%26nbsp%3B%3DINDEX(%24A%244%3A%24A30%2CRANK(B4%2C%24B%244%3A%24B%2430)%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample-%20Last%20names%20of%20Entrants%20are%20in%20Column%20A%20and%20I'd%20like%20to%20pull%20random%20names%20into%20column%20C%20to%20determine%20winners.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20have%20run%20into%202%20issues%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20If%20column%20A%20does%20not%20have%20text%20in%20all%20rows%20up%20to%2030%20it%20will%20return%20a%20value%20of%200.%20How%20can%20I%20make%20it%20so%20a%20user%20could%20enter%20in%20less%20than%2030%20names%20in%20column%20A%20and%20have%20excel%20skip%20blank%20ones%20in%20the%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Is%20there%20a%20way%20to%20allow%20a%20user%20to%20enter%20in%20how%20many%20winners%20they%20would%20like%20to%20have%20chosen%20and%20then%20have%20the%20Index%20function%20provide%20that%20many%20results%3F%20I've%20currently%20dragged%20formula%20down%20for%20my%20working%20version%2C%20but%20would%20like%20to%20make%20it%20easier%20and%20faster%20for%20users.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1412095%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-1412157%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Index%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677147%22%20target%3D%22_blank%22%3E%40rgrann12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20dynamic%20ranges%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX(%24A%244%3AINDEX(%24A%244%3A%24A%243000%2CCOUNTA(A%3AA)-1)%2CRANK(B4%2C%24B%244%3AINDEX(%24B%244%3A%24B%243000%2CCOUNTA(A%3AA)-1))%2C1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20C4%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20233px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F193958iF14164DF60B09C5C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412263%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Index%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BPerfect%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I've created a file to pull a random list from column A, utilizing Rand() in column B and the following Index Function in Column C: =INDEX($A$4:$A30,RANK(B4,$B$4:$B$30),1)

 

Example- Last names of Entrants are in Column A and I'd like to pull random names into column C to determine winners. 

 

Now I have run into 2 issues:

 

1. If column A does not have text in all rows up to 30 it will return a value of 0. How can I make it so a user could enter in less than 30 names in column A and have excel skip blank ones in the formula?

 

2. Is there a way to allow a user to enter in how many winners they would like to have chosen and then have the Index function provide that many results? I've currently dragged formula down for my working version, but would like to make it easier and faster for users.

3 Replies
Highlighted

@rgrann12 

You may use dynamic ranges like

=IFNA(INDEX($A$4:INDEX($A$4:$A$3000,COUNTA(A:A)-1),RANK(B4,$B$4:INDEX($B$4:$B$3000,COUNTA(A:A)-1)),1),"")

in C4

image.png

and drag it down

Highlighted

@Sergei Baklan Perfect thank you!

Highlighted

@rgrann12 , you are welcome