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.

DOWNLOAD WORKBOOK

Index_Interactive List

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

 

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Autosum an Array of Data in Excel When you have an array of data in Excel with Totals at the bottom and to the right of the data, you can quickly fill in the Totals with the Autosum button. STEP 1: Highlight your data including the "Totals" row and column; STEP 2: Click the Autosum button (under the Home or...
Advanced Sumproduct Function: Sum the Top 3 Sales ... This is probably the most advanced level a SUMPRODUCT function can reach and that is by including a nested array formula. In our example below we want to return the 3 Largest values from the North region and sum them up.  As we are asking our formula to perform multiple calcul...
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 ...
Excel´s TEXT Function   What does it do? Converts a numeric value to text and lets you specify the display formatting by using special format strings Formula breakdown: =TEXT(value, format text) What it means: =TEXT(a numeric value or a formula, a text string enclosed in quot...