1. Using VLOOKUP with data validation
Now that you have your named ranges and data validation sorted, it's time to add a VLOOKUP formula to the mix.
2. VLOOKUP
VLOOKUP means vertical lookup. It's perfect for retrieving data from tables. You can use it to find an exact match, or the closest possible match, in a list. The VLOOKUP will use the dates selected through data validation and look up the relevant data from the main dataset.
3. Adding the VLOOKUP
For VLOOKUP to work correctly your table must be sorted in ascending order by the first column.
As with all formulas in spreadsheets, help is there for you if you need it. Once you start typing in your VLOOKUP formula, a help box will appear with more information.
4. VLOOKUP arguments
The arguments for the VLOOKUP are search underscore key, range, index, and is underscore sorted. search underscore key is the value to look for in the first column of a table, or, in this case, the date the user has selected. range refers to the table from which to retrieve a value. index is the column in the table from which the value is to be retrieved. is underscore sorted is an optional argument, where FALSE means exact match and TRUE means closest possible match.
5. Partial absolution
Here's how it works. When you enter the formula in B3, the VLOOKUP will look at the date in A3, go to the named table in the dataset, and return the data in the specified column. In this case, you want the data for the Open column, column 2.
You will need to use partial cell absolution in your A3 reference of the formula. Absolution is denoted by a dollar sign. Placing a dollar sign in front of the column reference A will ensure that if you copy the formula you will always be referring to column A, but the row reference will change as you copy it down. You can think of it as locking column A. Since the row reference 3 is not locked in this formula, it will move when you copy the formula down the page.
6. Copying your formula
This slide shows the column reference 2 highlighted in the formula. The beauty of using partial cell absolution and a named range with this formula is that if you copied it one column to the right, the only part of the formula you need to change is the column reference that you wish the data to come from.
7. Pasting the formula
If you wanted to show the Close figure in column 5 of your dataset, you would change the column reference number in the formula from 2 to 5. If you wanted to show the Volume figure, you would change the 2 to a 6.
8. Creating the line plot
By using a combination of data validation and VLOOKUP, you can allow a user to pick dates from a list to show only the relevant values in your data snapshot and then create a chart from that.
A line chart represents a series of data points so, in this case, it is perfect for your task.
To create this chart, highlight the range you want to plot, including headings, click the Insert chart icon on your toolbar and select line chart.
Once you have the correct chart, resize it so it does not overlay your data.
9. Have a go!
Have a go at inserting a VLOOKUP formula into your dashboard and creating a line chart.