Revision history [back]

The process of obtaining a value from one column depending on the Effective Date found in another column can be done using a function called VLOOKUP.

VLOOKUP stands for "vertical lookup," and it allows you to search for a specific value in the first column of a table or range of cells, and then return a corresponding value from a different column in the same row.

To use VLOOKUP, you need to supply four pieces of information:

1. The value you want to find (in this case, the Effective Date)
2. The table or range of cells you want to search (which should include the Effective Date as well as the corresponding value you want to return)
3. The column number of the value you want to return (i.e., which column contains the value you want to extract)
4. The range lookup option (which determines whether the lookup should be exact or approximate)

Here's an example of how you might use VLOOKUP to obtain a value from one column depending on the Effective Date found in another column:

Suppose you have a table with three columns: Effective Date, Price, and Product. You want to be able to enter a specific date in a cell, and have Excel automatically look up the corresponding price for that date. Here's how you could do that:

1. Enter the date you want to look up in a cell (let's say it's in cell A1).
2. Select a blank cell where you want the corresponding price to appear (let's say it's in cell B1).
3. Type the following formula into cell B1:

=VLOOKUP(A1, table_range, 2, FALSE)

Here, "table_range" refers to the range of cells that includes the Effective Date, Price, and Product columns. The "2" tells Excel to return the value from the second column (i.e., the Price column), and "FALSE" tells Excel to look for an exact match (rather than an approximate match).

After you enter this formula and press Enter, Excel will search for the date you entered in cell A1, and then return the corresponding price from the second column of the table_range.