where “names” is the named range B5:B11, “rand” is the named range C5:C11, and “sort” is the named range D5:D11. The RAND function generates a random value at each row. Note: RAND is a volatile function and will generate new values with each worksheet change. The second helper column holds the numbers used to sort data, generated with a formula. The formula in D5 is: See this page for an explanation of this formula. The formula in E5 is: Here, the INDEX function is used to retrieve values in the named range “names”, using the sort values in the named range “sort”. The actual work of figuring out what value to retrieve is done my the MATCH function in this snippet: Inside MATCH, the ROWS function is given an expanding range as the lookup value, which begins as one cell, and expands as the formula is copied down the column. This increments the lookup value, starting at 1 and continuing to 7. MATCH then returns the position of the lookup value in the list. The position is fed to INDEX as the row number, and INDEX retrieves the name at that position.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
