Learn to use VLOOKUP to find the dates when a stock hit its minimum and maximum prices. The tutorial walks through the function's syntax with a concrete example: locating the date for a $44.96 minimum price. Set the sorted argument to FALSE when your price column is unsorted, or you'll get wrong matches.
Chapters
The first analysis you made on stock ABC allowed you to find the minimum and maximum historical values. Another question of interest about these extreme prices is when did they occur? This information is crucial for a more in depth analysis. For example, if you observe the minimum value in a period of a market downturn like during the financial crisis of 2008, you can associate it with the state of the economy. If the minimum occurred right after the company announced benefits below the market's expectation, then the drop in price reflects a company specific event. The spreadsheet function VLOOKUP can be used to find the date associated with a specific historical price. VLOOKUP stands for vertical Lookup. It has three mandatory arguments, a search key, a range of cells, and an index number, and one optional argument is sorted. The function searches the row corresponding to the search key in the first column of the matrix of input cells. Once found, it returns the element in the row corresponding to the index number specified as the third input argument. The search key can be a number, a string, or or a cell. In this case, it's the price $44.96 the minimum price. The second argument, the range, includes all the cells for the search. The range should include the search key. Here the range is a two column B10. The third argument, index, stands for the column index of the value to be returned where the first column in the range is numbered 1. In this case, the index is 2. As we want the function to return the dates in which the minimum price occurred. The last argument issorted is optional and indicates whether the first column of the specified range is sorted. In this case it's equal to false since the column with the prices is not sorted. Notice that if this argument is true or omitted and the first column of the range is not in sorted order, an incorrect value might be returned. The function outputs the value included in the cell at the intersection between row 10, the one that includes the search key, and column 2 of the range of cells. In the next exercises, you'll get more familiar with Vlookup to find the dates corresponding to the minimum and maximum historical prices. Let's practice.
