Index match where duplicates exist

[btwice]

Trying to do an index match, but because duplicate values exist, the formula stops once it finds the first value that matches. What I want to do is use secondary criteria to tell it to pull the value where it finds that criteria, and not just the first value it finds. See below for formula currently being used, as well as the criteria I want to use. I've done this before I feel like, but can't for the life of me remember how, thanks.

=index(VendorFiles[Package Type],match([@order],VendorFiles[Order],0)) - this just returns 'package type' for the first 'order' it finds

criteria to use - 'vendor'

So what I want it to do is this: if(row of 'Order' VendorFiles[Vendor] = [@Vendor], pull in 'Package Type',"Not Found")

Let me know if this makes sense/if I need to clarify, thanks.

Edit: found a way to do this by doing Match(order & vendor, VendorFiles[order] & VendorFiles[vendor],0), but it is unbelievably slow as the table I'm indexing has over 50k rows, so hoping for a better solution

 

[Vogateer]

These formulas can slow things down if you have a lot of data:

=INDEX(VendorFiles[Package Type],MATCH(1, (VendorFiles[Order]=[@Order]) * (VendorFiles[Vendor] = [@Vendor]),0))

The other option would be to create a new column in your VendorFiles table that combines Order and Vendor into one column. Maybe an Order_Vendor column like this:

=[@[Order]] & "_" & [@[Vendor]]

Then you just match for that:

=Index(VendorFiles[Package Type],Match([@order] & "_" & [@vendor],VendorFiles[Order_Vendor],0))

That gets around the first formula's slowness and the formula is easier to read.

 

[btwice]

Nice didn't think to just join those 2 values and use it that way. I added a column in the query that joins the two, query takes a little longer but I don't need to refresh it that often so should be good. Thanks!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)