Excel - How to get the current date set and assoc amount needed

0
votes

Need to determine current date in cell, then lookup in table - (2 cells) start and end dates, determine which the current date falls into, then pull the associated cell with rent amount.

see table:

Start Date  End Date    Rent Amount
5/1/2017    4/30/2018   $2,875.00
5/1/2018    4/30/2019   $2,961.25
5/1/2019    4/30/2020   $3,050.09
5/1/2020    4/30/2021   $3,141.59
5/1/2021    4/30/2022   $3,235.84

I don't have much experience with excel, I searched for examples cant find.

Say I am able to determine todays date is 5/17/2020 then I want to show the current rent is $3,141.59.

Start Date  End Date    Rent Amount
5/1/2017    4/30/2018   $2,875.00
5/1/2018    4/30/2019   $2,961.25
5/1/2019    4/30/2020   $3,050.09
5/1/2020    4/30/2021   $3,141.59
5/1/2021    4/30/2022   $3,235.84
1
excel-formula
I tried but I don’t think that is enough C1 has todays date: =TODAY() I am trying to ref this cell C1, and using that date determine which set of info in the table in B12 thru C16 the date falls between, then select the corresponding amount in table then looking table D12 thru D16: – RobertOzone

1 Answers

0
votes

You can use a VLOOKUP

In the picture below Column F has the formula: =VLOOKUP(E2,B2:D4,3,FALSE)

  1. First Parameter in function is the thing you want to search
  2. Second in the range of column you want to search in
  3. Third in the column index you want to return on match (1 based)
  4. Fourth is whether it has to be exact or partial match.

Do remember that VLookup by default will search only the first column for match.

enter image description here