Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
May 03, 2021
Solved

Vlookup with Today()

Hello Experts,   I need a lookup that will return a value based on todays date.   The tricky part is that my table is in month and year format and not days.   Also, I think having the dates in ro...
excel
Tony2021's avatar
Tony2021
Steel Contributor
May 03, 2021
Hi Sergei, thanks for the great response. I have a question though.
When I put that formula in my excel it returns a message of "Theres a problem with this formula..."
do you know why? I dont get the error in your file but I copy and paste and change the name of the table only to my table name.

Here is what I have: (note only change is Table1 ==> OutstandingTbl
=INDEX(OutstandingTbl,
MATCH(A2,OutstandingTbl[Beneficiary],0),
MATCH(TEXT(EOMONTH(TODAY(),-1)+1,"m/d/yyyy"),OutstandingTbl[#Headers],0))
  • Tony2021's avatar
    Tony2021
    Steel Contributor
    May 04, 2021

    SergeiBaklan 

    Hi Sergei and Peter, many thanks for your assistance. I have found the issue. I seem to not be able to rename the table to Outstandingtbl. I click the table and go to table design and change the name but it doesnt stick. I had to use Table7, the default name and after I did the formula works perfectly.

     

    I do have a follow up though and not certain if I can continue here or not but I assume its ok.

    I need to add a condition on a field for [Issued or Pending] which is on the same sheet as the formula. If its status is Issued then return the value but if not then return "Pending". Not sure if returning a text in a number field is a good idea but if I have an issue I can change it.

     

    I have uploaded a new file with the new column Issued or Pending.  

     

    thank you very much. 

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 04, 2021

      Tony2021 

      Sorry, I didn't catch. We would like to return status here

      or column B is the status and depends on it we return in C value or not? If the former how do we know that's issued or pending?

       

      • Tony2021's avatar
        Tony2021
        Steel Contributor
        May 04, 2021
        Hi Sergei, sorry for not being more clear on that. the status does depend on if a value is returned or not. If its pending then no value but if its Issued then return the value.

        I hope its clear. let me know.
    • Tony2021's avatar
      Tony2021
      Steel Contributor
      May 04, 2021
      Oh and regarding the table name...I had 2 names and I deleted both and started all over with naming the table and the name sticked.