SOLVED

Vlookup with Today()

%3CLINGO-SUB%20id%3D%22lingo-sub-2319365%22%20slang%3D%22en-US%22%3EVlookup%20with%20Today()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319365%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20lookup%20that%20will%20return%20a%20value%20based%20on%20todays%20date.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20tricky%20part%20is%20that%20my%20table%20is%20in%20month%20and%20year%20format%20and%20not%20days.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20think%20having%20the%20dates%20in%20row%201%20could%20create%20an%20issue%20but%20not%20sure%20about%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached%20and%20let%20me%20know%20if%20you%20have%20any%20questions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGrateful%20for%20the%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2319365%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2319562%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20Today()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(Table1%2C%0A%20MATCH(A2%2CTable1%5BBeneficiary%5D%2C0)%2C%0A%20MATCH(TEXT(EOMONTH(TODAY()%2C-1)%2B1%2C%22m%2Fd%2Fyyyy%22)%2CTable1%5B%23Headers%5D%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2319929%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20Today()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2319929%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%20thanks%20for%20the%20great%20response.%20I%20have%20a%20question%20though.%3CBR%20%2F%3EWhen%20I%20put%20that%20formula%20in%20my%20excel%20it%20returns%20a%20message%20of%20%22Theres%20a%20problem%20with%20this%20formula...%22%3CBR%20%2F%3Edo%20you%20know%20why%3F%20I%20dont%20get%20the%20error%20in%20your%20file%20but%20I%20copy%20and%20paste%20and%20change%20the%20name%20of%20the%20table%20only%20to%20my%20table%20name.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20what%20I%20have%3A%20(note%20only%20change%20is%20Table1%20%3D%3D%26gt%3B%20OutstandingTbl%3CBR%20%2F%3E%3DINDEX(OutstandingTbl%2C%3CBR%20%2F%3EMATCH(A2%2COutstandingTbl%5BBeneficiary%5D%2C0)%2C%3CBR%20%2F%3EMATCH(TEXT(EOMONTH(TODAY()%2C-1)%2B1%2C%22m%2Fd%2Fyyyy%22)%2COutstandingTbl%5B%23Headers%5D%2C0))%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2321081%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20with%20Today()%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2321081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20renamed%20the%20table%20in%20attached%20file%2C%20try%20to%20copy%2Fpaste%20formula%20from%20it%2C%20not%20from%20the%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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. 

 

14 Replies

@Tony2021 

That could be

=INDEX(Table1,
 MATCH(A2,Table1[Beneficiary],0),
 MATCH(TEXT(EOMONTH(TODAY(),-1)+1,"m/d/yyyy"),Table1[#Headers],0))

@Tony2021 

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.

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))
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?

@Tony2021 

I renamed the table in attached file, try to copy/paste formula from it, not from the post.

@Sergei Baklan 

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. 

 

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.

@Tony2021 

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

image.png

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?

 

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 

If I understood the logic correctly you may apply custom number format (Ctrl+1) to returned results as

image.png

@Sergei Baklan 

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.

best response confirmed by Tony2021 (Contributor)
Solution

@Tony2021 

If in opposite direction that's like

=IF(E2="Issued", INDEX(....), 0)

Please check in attached. 

Perfect. Thank you for the assistance. Much appreciated.

@Tony2021 , glad it helped, you are welcome