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
markjoyus
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 

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! 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies