Help with Index Match Function (or other function)

Hey folks, thanks for any help in advance!

Also, in advance, (as this is my first time posting here), I may over explain and I apologize, I just want to make sure you get a good visual representation.

I'm working on a document where I have multiple sheets pulling data from our database (PO Table, Work Order Table, ...etc)

Right now I'm trying to index match using multiple criteria. I want to index the PO number from the PO table onto the CC Data table, but only if both the dates and the amounts match.

Perhaps an index match function isn't the best route to take here, so I'm open to suggestion.

CC Data Table example:
A B C D
Date Name Credit PO#
09/04/2019 Alldata Corp 310.00

PO Table example:
A B C D E F G
disp-no Po-no Name Po-date Term-code Remit-vendor Open-value
291962 80135 Roofing Supply of Knoxville RS 9/4/2019 DUE 4533 150

Thanks! Here's a simplified version with only two sheets and only a small fraction of the data. Hopfully this helps?

Also, I tried to do this before, but wasn't sure how to upload attachments. Thanks for the help with that. lol

I've tried nesting an AND statement inside of the index match function, and either it doesn't work, or I'm doing it wrong, (likely the latter). I've also tried just doing "=IF(AND(this=this,this=this),1,0)" however, because it's going across two different sheets within the document, it's not returning the way I want it to. Also, I didn't really expect that to function the way I wanted it to, to begin with. That function breaks down really quickly over tens of thousands of pieces of data - lol

Thanks for that -- it appears to be giving me the row number rather than the PO number in the E:E on the CC Table.
Perhaps I need to format the data differently? In the PO table it's giving values like this "43712150"

CC TABLE

D2=IFERROR(INDEX(Table1[Po-no],MATCH([@Name]&[@Date]&[@Credit],Table1[Name]&Table1[Po-date]&Table1[Open-value],0)),"NO PO")

control+shift+enter

copy down

Yeah, the numbers are stored as text because that's how they are stored in the database, (this data is being pulled directly from our SQL server), I don't know why the devs of the database program we use decided to store them as text, but they did. It makes things like this a nightmare for me because I always forget about that. lol

This works, but when I change from " 'PO Table'!$B$2:$B$300 " to " 'PO Table'!B:B", ...etc, it no longer works. This is an on-going project where new data will be coming in every day, so I don't really want to limit it to a number, especially as of right now there are several thousand rows of data. This formula also seems to have a REALLLYYY slow calculate time, which really makes this whole report run like butt. :/

Even when changing this to the correct table names, I seem to only get errors. I can't get this to work at all, unfortunately.

Any other thoughts? What Richard put seems to work the best, however, It's not showing the PO number, only the row number.

All ranges must be the same size. It is better not to use full-column references with SP - even limiting it to 50,000 or 100,000 rows will significantly improve the calculation time.

If you concatenate columns D C and G in a helper column, then you could use SUMIFS, which should be quicker still.

Hope this helps.

Pete

I see! You're right, that dramatically improved time. Thanks a lot! I think this will work for now. You all have been a great help! Thanks! I don't fully understand how that formula works, I'm going to have to research a fair amount, but it works, so that's all that matters, I suppose. lol

Actually one last thing, is there a way to do the same thing but on the PO Table?
To explain what's happening here, the PO table is pulling from our database, the CC table that's there is pulling from our bank. My boss wants me to get this report set up in such a way that we can tell if the credit cards are being reconciled the correct way in the database, because it seems very much like they aren't.
I just got off of the phone with him, what he asked was can we get it set up so that on the PO table, there's a column that shows whether or not the CC was reconciled correctly.
So, if it matches, instead of pulling over the PO number, it would just show a 1 and a 0 or something.

I'm super sorry, I feel like I've been a pain in the rear.

Thanks again for everything!

Caracalla, this just produces all zeros on the PO table.

This is my file

Even looking at yours, it doesn't seem right, perhaps I'm seeing something wrong. There are 10 on the CC table with PO numbers, but only 9 matching on the PO table?

Because in cc table

08/27/2019 Refco supply 119.63 79993


08/27/2019 Refco supply 119.63 79993

is double

Ohhhhh. I see. Like I said, I'm probably seeing something wrong! haha

I'll see if I can get this working on the real version, I don't know why I was having problems with it. lol

Soooo, when I put in your formula it just throws out "no po" for everything. I genuinely don't understand. *scratches head*
I did have to change it to match the correct info, but it's still throwing errors.

=IFERROR(INDEX(Table_Query_from_Davisware[@[Po-no]],MATCH([@Name]&[@Date]&[@Credit],Table_Query_from_Davisware[@Name]&Table_Query_from_Davisware[Po-date]&Table_Query_from_Davisware[Open-value],0)),"NO PO")

Attach the real file

Can I remove it right after? It's got a lot of customer data on it.

Have you confirmed with control + shift + enter?

I have confirmed CTRL, Shift, ENT. I'm working on anonymising right now, I'll have it to you in a moment.

ugh - I'm trying to get the file size low enough to get it to you - lol.
The max file size is 1MB, this is 18MB lol

This just apparently isn't possible - I just removed several hundred thousand rows of data - still too big. lol

Every time I try to upload the xlsb it fails.

it's only 2.71MB

Hey there! Sorry to bother, I added a link to this, hopefully you can get it to work? :/

Thanks so much!

Gotcha, sorry! I'm getting that a done now.
I'll have it uploaded here in a moment.

Lets try this one! This is the exact same document I'm using, the only difference is the date range is smaller and the names/addresses have been obfuscated.

Thank you again. <3

That's an elegant solution! I really like that, and it works really well! Do you have an idea on the second question I had, which is making the PO table display a 1 or 0 (or any other true or false value) if there's a CC payment that matches?
Basically the same thing here did here, only on the PO side.

What I did for now is "=COUNTIF(I:I,'CC Table'!L:L)"
There are a few that have duplicate values that show a "2", but for the most part this gives me the "true,false" that I was looking for on the PO table.
If you have a different solution, that would handle this a little more gracefully, I'd greatly appreciate it, however, this seems to get me going from here.

My boss really wants something that shows words, I suppose I could do an if statement saying if 0 "no cc" and if anything else show something else. I dunno, yet.

In any case, thanks so much for all of your help.