Need to Pull More Numbers (VLOOKUP, INDEX, etc. not working)

Copper Contributor

SOLVED: I found a way to get the VLOOKUP to work properly (and not return an error) by nesting TEXT within; thus, I was able to ditch the FILTER function for column D!

So I previously received some help pulling column B correctly (it was repeating values). However, I now have an issue in column D of my help workbook (link below). The first two rows are pulling correctly, as I have manually verified that these belong to the same invoices. However, you can see in the help workbook that the matching value (manually verified) for row 3 is showing up on row 4; these are manually verified by matching vendors, and the value in row 3 is actually for Vendor 1 and I am trying to pull only for Vendor 2.

 

I appreciate any help! I have tried many different formulas (and plenty of nesting) and helper columns and I cannot seem to get it right.

Note: The two vendors need to stay the way they appear in the main columns (A:D) of the 'LAST EXP' tab, as that is how they will export from the software we use; I am building this so the end-users do not have to manually edit that export beyond copying and pasting into this help workbook.

 

Audit Help Workbook

4 Replies

@gem333 

Again, I advise you to use a pivot table.

And do not copy the exported data from your third party software but use Power Query to import and manipulate the data.

 

The formula in column D does NOT refer to an invoice number - only to the start date and end date.

 

I am currently working within the parameters I was given for the project, and I was specifically asked to get a workbook up and running in which the manager could export, copy and paste. Until I am able to work outside of the current parameters, the exported data is the only way I can follow the contract of the project.

As for the formula not referencing the invoice number, I tried to find a way to have it reference and that is what I am struggling with. If you have an answer as to how to get that to reference properly all the material it needs to, then I would love to hear it.

@gem333 

In the attached file i've entered FILTER formulas in cells A7 and D7. The results for the invoice amounts have a difference of 514,50 which is from the LAST EXP sheet ordered 11.02.2022 by Vendor 2.

 

Hello! This worked nicely for a few of the rows. Luckily I do not need the order from the 11th. However, I may not have been as precise as I could have in my original post - I need the total from the LAST EXP sheet to show based on how it matches up with both the date and PO #, regardless if the amount is equal to that in column C.

I see now that the attachment on my original post was not the help file that had the PO #s entered.
Here is the help file with PO#s: https://1drv.ms/x/s!AjrdN6E65dMegYZ9sFnWpGFGyIi4lw?e=Btlr1D

I'm not married to the use of the formula currently sitting in there, if there is a better option!