VLookup In Reports Downloaded From SAP & AS400

Report 1 is downloaded from SAP into Excel and Report 2 is downloaded from AS400 into Excel.

Report1 contain 3 columns as follows:
Column A - Invoice #
Column B - Invoice date
Column C - Invoice Amt

Report2 contain 5 columns as follows:
Column A - Invoice #
Column B - Ship Date
Column C - Check #
Column D - Amt Paid
Column E - Payment date

I tried to use the Vlookup function in Report 1 Column D to lookup the matching Invoice # in report 2 and bring in the corresponding Amt paid. It did not work and I assume that this is because of the formats in the downloaded files. I did try to change formats for the columns involved but this did not work. This function works fine if both files were setup in Excel, however in these downloaded files even though saved as excel workbooks, something prevents this function from working. I do know for a fact that the same invoice number does show up in both these files but the vlookup does not pull up the required Amt paid from Report 2.