Forum Discussion
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 row 1 could create an issue but not sure about that.
Please see attached and let me know if you have any questions.
Grateful for the help.
If in opposite direction that's like
=IF(E2="Issued", INDEX(....), 0)
Please check in attached.
14 Replies
I might set the defined name 'today' to refer to
=(TEXT(EOMONTH(TODAY(),-1)+1, "m/d/yyyy"))
then perform an exact match on the table
= XLOOKUP( today, Table1[#Headers], Table1)
or the equivalent in old Excel.
That could be
=INDEX(Table1, MATCH(A2,Table1[Beneficiary],0), MATCH(TEXT(EOMONTH(TODAY(),-1)+1,"m/d/yyyy"),Table1[#Headers],0))- 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))I renamed the table in attached file, try to copy/paste formula from it, not from the post.
