#N/A in approximate match VLOOKUP (with Dates)

%3CLINGO-SUB%20id%3D%22lingo-sub-1359827%22%20slang%3D%22en-US%22%3E%23N%2FA%20in%20approximate%20match%20VLOOKUP%20(with%20Dates)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1359827%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20just%20going%20crazy%20here%20with%20about%20this%20Vlookup%20again%20and%20again%20on%20this%20report%20I'm%20trying%20to%20do.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20spreadsheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20am%20using%20a%20vlookup%20on%20the%20table%2C%20I'm%20trying%20to%20use%20the%20%22Cycle%20Date%22%20in%20the%20first%20column%20to%20return%20are%20the%20result%20in%20the%20second%20column%26nbsp%3B%22Cycle%20Date%22.%20while%20looking%20through%20%22Workings%20(Cycle%20Dates)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20formula%20seems%20to%20work%20form%20some%20the%20cells%2C%20but%20I%20still%20cant%20figure%20it%20out%20it%20is%20not%20working%20for%20all%20of%20them.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22630px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22117px%22%3ECycle%20Date%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3ECycle%20Date%3C%2FTD%3E%3CTD%20width%3D%22123px%22%3EWorkings%20(Cycle%20Dates)%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22117px%22%3E29%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E%23N%2FA%3C%2FTD%3E%3CTD%20width%3D%22123px%22%3E29%2F09%2F2017%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E29%2F09%2F2017%20-%2028%2F10%2F2017%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3EOct-17%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22117px%22%3E29%2F03%2F2020%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E%23N%2FA%3C%2FTD%3E%3CTD%20width%3D%22123px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3ENov-17%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22117px%22%3E29%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E29%2F01%2F2020%20-%2028%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%22123px%22%3E29%2F10%2F2017%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E29%2F10%2F2017%20-%2028%2F11%2F2017%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3ENov-17%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22117px%22%3E29%2F01%2F2020%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E29%2F01%2F2020%20-%2028%2F02%2F2020%3C%2FTD%3E%3CTD%20width%3D%22123px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22167px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1359827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1359950%22%20slang%3D%22en-US%22%3ERe%3A%20%23N%2FA%20in%20approximate%20match%20VLOOKUP%20(with%20Dates)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1359950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53500%22%20target%3D%22_blank%22%3E%40Kwesi%20Quartey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20formulas%20you%20use%20EXACT%20match%2C%20not%20approximate%20one%26nbsp%3B%3DVLOOKUP(A2%2CC%3AD%2C2%2C%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E0%3C%2FSTRONG%3E%3C%2FFONT%3E).%20Please%20note%20for%20approximate%20match%20lookup%20array%20shall%20be%20sorted.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1359970%22%20slang%3D%22en-US%22%3ERe%3A%20EXACT%20in%20match%20VLOOKUP%20(with%20Dates)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1359970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BHi%20Sergei%2C%20sorry%20I%20had%20made%20a%20typo.%20yes%2C%20you%20are%20correct%20I'm%20using%20an%20%3CSPAN%3EEXACT%20match%20not%20an%26nbsp%3Bapproximate%20one.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Everyone 

I'm just going crazy here with about this Vlookup again and again on this report I'm trying to do. 

I have attached a spreadsheet. 

 

I'm am using a vlookup on the table, I'm trying to use the "Cycle Date" in the first column to return are the result in the second column "Cycle Date". while looking through "Workings (Cycle Dates)"

 

the formula seems to work form some the cells, but I still cant figure it out it is not working for all of them. 

 

Cycle DateCycle DateWorkings (Cycle Dates)  
29/03/2020#N/A29/09/201729/09/2017 - 28/10/2017Oct-17
29/03/2020#N/A  Nov-17
29/01/202029/01/2020 - 28/02/202029/10/201729/10/2017 - 28/11/2017Nov-17
29/01/202029/01/2020 - 28/02/2020   
6 Replies

@Kwesi Quartey 

In your formulas you use EXACT match, not approximate one =VLOOKUP(A2,C:D,2,0). Please note for approximate match lookup array shall be sorted.

@Sergei Baklan Hi Sergei, sorry I had made a typo. yes, you are correct I'm using an EXACT match not an approximate one. 

@Kwesi_Quartey 

For the exact match formula returns correct results.

@Sergei Baklan thanks, but I'm still getting "N/A" in some of the fields, which shouldn't be. I've looked some of the old reports and they don't have it. maybe it's me or I don't seem to understand what it is.

@Kwesi_Quartey 

An error is only for one date

image.png

which is not in the list

image.png

@Sergei Baklan hmmm, thanks again. I think there's an oversite on my side. let me try and figure this out.