Google-sheets – Changing cell based on date in another cell

google sheets

I'm trying to work out a price sheet for some equipment I'm buying. I would like to have a cell change it's date based on today's date that's in columns so it can show me today's date as a quick reference and to fill in another cell on a different page to compare prices between purchase locations. Just can't figure out the formula for B3:B7 to get what I want.

Example is pictured.

enter image description here

Best Answer

The secret to such a formula is to use HLOOKUP.

Here's how you can create a formula for B3:B7 to look up the current price based on today's date.

  1. Select cell B3
  2. Enter: =hlookup
  3. Select HLOOKUP from the list that came up during the previous step
  4. Now that you're being prompted for the search_key, enter (with trailing comma): TODAY(),
  5. To select the range of dates and prices enter: C$1:I2,
  6. To select which row to pull the price from (the index) enter: 3,
  7. Finally, since the dates are sorted enter: TRUE)
  8. Press ENTER to accept the formula

So that takes care of the first equipment. As for the remaining ones...

  1. Select cell B3.
  2. Drag the square in the lower-right-hand corner of the cell down to cell B7.
  3. Select cell B4 and change for index parameter in the formula to 4.
  4. Repeat step 3 for B5 and so on, but increment the index each time.

Here is a working example: https://docs.google.com/spreadsheets/d/1XDQcc5YeRN2W7ZrJmrwx_3ai9bMlZiAtVnKmOyJ-lp8/edit?usp=sharing

Related Topic