May 03 2021 03:10 PM
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.
May 03 2021 03:39 PM
That could be
=INDEX(Table1,
MATCH(A2,Table1[Beneficiary],0),
MATCH(TEXT(EOMONTH(TODAY(),-1)+1,"m/d/yyyy"),Table1[#Headers],0))
May 03 2021 03:40 PM
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.
May 03 2021 07:22 PM
May 03 2021 10:41 PM
May 04 2021 03:18 AM
I renamed the table in attached file, try to copy/paste formula from it, not from the post.
May 04 2021 05:18 AM
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.
May 04 2021 05:20 AM
May 04 2021 07:17 AM
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?
May 04 2021 12:32 PM
May 04 2021 01:09 PM
If I understood the logic correctly you may apply custom number format (Ctrl+1) to returned results as
May 04 2021 01:31 PM
very interesting. I however have not made myself clear. I do not need to combine the fields as show but that is an interesting concept.
I simply need in the 3rd column for Value, an amount if the second column has issued in it and if it is pending then return 0. Please see attached.
thank you. I hope this clarifies, and feel free to let us know if you need further clarifications.
May 04 2021 02:05 PM
SolutionIf in opposite direction that's like
=IF(E2="Issued", INDEX(....), 0)
Please check in attached.
May 04 2021 06:30 PM
May 04 2021 02:05 PM
SolutionIf in opposite direction that's like
=IF(E2="Issued", INDEX(....), 0)
Please check in attached.