Vlookup formula not working for the attached spreadsheet

[riajul]

Can anyone give me a solution why vlookup formula is not working for the attached file.
I need to set quarter against dates.
Quarter table is also attached.

 

[GraH - Guido]

riajul,
No file however available for us to download ... Please try again.

 

[riajul]

apologies, please see now

 

[riajul]

If you can't see the file, please click on the link

https://docs.google.com/spreadsheets/d/1e9cHae9tWb2jXJ7rBoMByoBTZkqjcz0JF5h6GaJdA1Y/edit?usp=drive_link

 

[p45cal]

You need to make that link public - currently it needs a login and granted access.

 

[riajul]

formula Vlookup not working

Quarter Quarter Start,Quarter End,Quarter & Year 01/04/2020,30/06/2020,Q1-2020-21 01/07/2020,30/09/2020,Q2-2020-21 01/10/2020,31/12/2020,Q3-2020-21 01/01/2021,31/03/2021,Q4-2020-21 01/04/2021,30/06/2021,Q1-2021-22 01/07/2021,30/09/2021,Q2-2021-22 01/10/2021,31/12/2021,Q3-2021-22 01/01/2022,31/03...

docs.google.com

 

[p45cal]

The dates in the Test Date column are not true dates, only text that looks like dates.
Convert them to real dates, then test the formula:
=VLOOKUP([@[Test Date]],Table_1,3)
See attached (removed rows 25000 and above to reduce file size).

 

[riajul]

Thanks a lot, but how you converted text into date?

 

[p45cal]

Thanks a lot, but how you converted text into date?

By using Text to Columns.
Go to the section:

Using the Text to Columns Feature to Change the Format​

at https://www.excelcampus.com/tips/text-to-dates-text-to-columns/

In Step 3 of 3 it's important to select DMY since that tells Excel how your SOURCE data is laid out:

 

[riajul]

Many thanks