To make the INDEX function in Excel interactive we can add a Data Validation drop down list.

By referencing the second argument in the INDEX function – the Row Number – to a data validation list, will allow a user to choose the nth position of a list and return their respective metrics.

Say, for example, we had a list of Top 10 customers with their respective Sales and Units sold.  A user can choose a number from the drop down list and return the customer name, their sales and units sold – all with the power of the INDEX function.

See how this is achieved in the below animated gif.


Index_Interactive List




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

IF Function: Introduction The IF function is probably one of the most used Excel functions because it is easy to understand and very flexible when you apply it to real life situations.Here I will show you a couple of ways that you can use the IF function to get you up and going.DOWNLOAD WORKBOOK ...
Jump To A Cell Reference Within An Excel Formula When writing, editing or auditing Excel formulas you will come across a scenario where you want to view and access the referenced cells within a formula argument.This is helpful if you want to check how the formula works or to make any changes to the formula.There is ...
Return the Last Value in a Column with the Offset ... What does it do?It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cellsFormula breakdown:=OFFSET(reference, rows, columns, , )What it means:=OFFSET(start in this cell, go up/down a number of ro...
Calculate Elapsed Time in Excel When you have two points in time and you want to calculate the amount of time elapsed, then you will need to use Excel´s TEXT functionSometimes data gets dumped into Excel with the following date & time format:24/01/2015  19:48:00.Using the TEXT function and enter...