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 Combined With The AND Function When combining (or nesting) the AND function with the IF function, it allows you to add more than one condition to your formula, something that is not possible with the IF function by itself.So you can show the results of Sales Reps that have made more than $3,000 of sales AN...
Advanced SUMPRODUCT Function: Conditional Date If you want to find out the total sales for a particular month, then the SUMPRODUCT function is your answer.  You can create a criteria for a specific date range, a particular month or a year.In the example below I show you how to use the SUMPRODUCT function to sum up the tot...
Excel Subtotal Function – Filtered or Visible Valu... What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...
Excel´s EndOfMonth function The EOMONTH (EndOfMonth) function in Excel is one that most people do not use because they just don't know that it exists.It is a great Excel function to use if you want to see when the month end date is from a current date's value.So if you have sales reps who make a sal...