Select dataset based on drop down value

Hello all. In this tutorial, let’s look at returning a complete data set based on selected string as drop down value. This tutorial can home handy when you want to pull standard set of metrics for different department in an org or if you want to pull meta-data about certain product’s sales numbers.

Let’s work on the below data set

Here we have sales data for different product across different time period (here, each quarter). We are going to make a drop down with product names (Alto, Ritz, Swift, Ciaz) and based on the selected product, we need the sales data (unit sold, Top Variant, Top Region) to be filled in or populated automatically.

First, let’s make a simple list of cell reference depicting the sales data for each product. For instance, the data for Alto is B3:D12.

Let’s use data validation method to create the drop down of products. We use O3:O6 to create the drop down

In our case, the date range (Q1 2018 to Q2 202) and the sales data headings (units sold, top variant, top region) is constant. Let’s copy these data and paste @ A17

Now, let’s put the below formula in B18

=INDIRECT(VLOOKUP(B15,O3:P6,2,0))

In this formula, the vlookup looks for B15 which is our drop down (one of the products), lookup array is cell reference that we created in the first step and column index number is the cell range for each product’s sale data. So Vlookup would return B3:D12 for Alto, E3:G12 for Ritz etc. We are using indirect function with the Vlookup – remember indirect would return the reference range for the given text. If the drop down value is selected is “Alto”, vlookup would return B3:D12 and indirect function would just return the values in these cell range.

Whenever the value is changed in the drop down, the corresponding sales values will be returned automatically.

Video tutorial

I hope you like this tutorial. Please provide your valuable feedback which will help in improving the content. Thanks for reading.

Leave a Reply

%d bloggers like this: