-1

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

0

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

0

With a similar lookup principle less work is involved to have the automatic relative referencing adjustment make the necessary changes from one row to the next, in B3 and copied down to suit:

=index(C3:I3,match(today(),C$1:I$1,0))

INDEX
MATCH
TODAY

Note that TODAY is driven by the time offset setting for the sheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.