Highlighted
New Contributor

# Need help with Index Function

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

# Re: Need help with Index Function

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

and drag it down

Highlighted

# Re: Need help with Index Function

@Sergei Baklan Perfect thank you!

Highlighted

# Re: Need help with Index Function

@rgrann12 , you are welcome