In this tutorial we’ll look at how to randomly pair two teams from a list of 10, five from each of two countries, but with the condition that we can’t pair two teams from the same country. We’ll use IF, RANDBETWEEN, and CHOOSE functions to accomplish this. Here we go!
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here we have a list of 10 hockey teams in random order, 5 each from the USA and Canada:
I’ve created two options – one will run the volatile RANDBETWEEN formula every time F9 is hit, the other requires manually entering a team number for the top team:
Cell E3 just has a simple RANDBETWEEN formula which will choose a number from 1 to 10:
Then cells F3 and G3 use basic VLOOKUP formulas to pull the team name and country from our list:
The key is the formula in cell E4 that randomly chooses a pairing for the team from E3, without pairing it from the same country. That formula is:
Let’s break it down. It’s a basic IF statement that says IF the team from the first row is from CANADA, then run this CHOOSE formula:
That will generate a team number from the USA.
However, if the team from the first row is NOT from CANADA, then run this CHOOSE formula:
And that will choose a team from CANADA.
The CHOOSE function has the following syntax:
=CHOOSE(index_num,value1, value2, value3…)
So our index number is generated by the RANDBETWEEN function which will pick a number from 1 to 5. That number will be associated with the 5 numbers, or “values” that are the teams either from the USA (1,4,5,8, or 10)or CANADA (2,3,6,7, or 9).
It’s a pretty simple process. If you want to see me create the formula from scratch, take a few minutes and watch the video!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!