SOLVED

Vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-898060%22%20slang%3D%22en-US%22%3EVlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898060%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20for%20dates%20in%20an%20Excel%20report%20to%20match%20dates%20downloaded%20from%20a%20web%20report%20and%20subsequently%2C%20bring%20back%20a%20value%20several%20rows%20over.%20A%20typical%20Vlookup%20function.%20I've%20tried%20formatting%20both%20columns%20of%20dates%20the%20same%2C%20but%20no%20matter%20what%20I've%20tried%2C%20the%20dates%20in%20the%20Excel%20report%20don't%20match%20the%20dates%20from%20the%20web%20and%20I%20get%20%23N%2FA%20as%20a%20result.%20Can%20you%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-898060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898158%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898158%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421625%22%20target%3D%22_blank%22%3E%40markjoyus%3C%2FA%3E%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20type%20of%20formatting%20did%20you%20choose%20for%20both%20the%20columns%3F%20It%20may%20help%20to%3A%3C%2FP%3E%3COL%3E%3CLI%3EClear%20both%20columns%20of%20any%20formats%20and%20then%3C%2FLI%3E%3CLI%3EReapply%20the%20date%20format%20you%20want%3C%2FLI%3E%3CLI%3EUse%20the%20Vlookup%20function%20with%20False%20(exact%20match)%3C%2FLI%3E%3C%2FOL%3E%3CP%3EAlternatively%2C%3C%2FP%3E%3COL%3E%3CLI%3EClear%20the%20columns%20of%20any%20formats%20and%3C%2FLI%3E%3CLI%3EUse%20the%20Vlookup%20function%20based%20on%20the%20numbers%20(for%20those%20dates)%3C%2FLI%3E%3C%2FOL%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898230%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F155429%22%20target%3D%22_blank%22%3E%40Genevieve%20Volkers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20work%20with%20the%20data%20(dates)%20in%20the%20web%20report%2C%20but%20the%20data%20that%20is%20sent%20to%20me%20as%20a%20.xls%20file%20will%20not%20budge%20when%20I%20try%20to%20change%20the%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898248%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898248%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20about%20resaving%20that%20file%20and%20choose%20specifically%20the%20.xlsx%20format%3F%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421625%22%20target%3D%22_blank%22%3E%40markjoyus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899577%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F155429%22%20target%3D%22_blank%22%3E%40Genevieve%20Volkers%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20tried%20saving%20as%20a%20.xlsm%2C%20a%20.xlsx%20and%20it%20didn't%20make%20any%20difference.%20However!%20I%20saved%20as%20a%20.txt%20and%20was%20able%20to%20copy%20and%20paste%20over%20the%20dates.%3C%2FP%3E%3CP%3EThanks%20for%20your%20input!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm looking for dates in an Excel report to match dates downloaded from a web report and subsequently, bring back a value several rows over. A typical Vlookup function. I've tried formatting both columns of dates the same, but no matter what I've tried, the dates in the Excel report don't match the dates from the web and I get #N/A as a result. Can you help?

4 Replies

Hi @markjoyus ,

 

What type of formatting did you choose for both the columns? It may help to:

  1. Clear both columns of any formats and then
  2. Reapply the date format you want
  3. Use the Vlookup function with False (exact match)

Alternatively,

  1. Clear the columns of any formats and
  2. Use the Vlookup function based on the numbers (for those dates)

Hope this helps.

@Genevieve Volkers 

I can work with the data (dates) in the web report, but the data that is sent to me as a .xls file will not budge when I try to change the formatting.

How about resaving that file and choose specifically the .xlsx format? @markjoyus 

Best Response confirmed by markjoyus (New Contributor)
Solution

@Genevieve Volkers

I tried saving as a .xlsm, a .xlsx and it didn't make any difference. However! I saved as a .txt and was able to copy and paste over the dates.

Thanks for your input!