Forum Discussion
Vlookup with Today()
If in opposite direction that's like
=IF(E2="Issued", INDEX(....), 0)
Please check in attached.
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.
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.
- The formula is correct and works when copied to Excel. That leaves a question of is the Table set up as you say? Does the array formula
=OutstandingTbl[Beneficiary]
work in isolation?
