Lookup with multiple returns for blocks of unique Identifiers.

[billbalint]

Hello,

I am looking for a way, I've been kicking around INDEX MATCH, to create a formula that will accomplish what I have in Column P (manually)

The Order ID and Invoice Number are identical, however, they are not unique. I know index match can do this, frozen to a lookup value, but is there a way to make it dynamic so that I can drag the formula in P?

Vlookup, of course returns the first found value relating to the lookup value in all instances in column N but they need to change based on the 2nd, 3rd, 4th ect....instances of the same ID. I do not have the data in Column E on the dataset in L:P. If I did, a simple VLOOKUP would do the trick.

I attached a JPEG and a sample of the dataset in the same format. The actual workbook is probably better as it contains more examples of multiple different items under one Order ID/InvoiceNumber.

The whole dataset is so large, it will take a week to do it manually.

Thank you for your help!




MultipleReturns.jpg

[billbalint]

The actual dataset, is so large, that is. This is a sample to accomplish the same thing.

[AliGW]

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[Glenn Kennedy]

One way:
=IFERROR(INDEX(G:G,AGGREGATE(15,6,ROW($G$2:$G$36)/(($D$2:$D$36=M2)*($G$2:$G$36<>"")),COUNTIF(M$2:M2,M2))),"")

copied down. If you have )365, there MAY be a better way.

[billbalint]

I do have Office 365

[Glenn Kennedy]

OK.

1. Please amend your profile to show O365 and the version number (model it on mine)

2. Check the solution that I have already offered. Does it deliver the required results?

[Glenn Kennedy]

looking at this again, I'm not sure if a dynamic array would offer any obvious advantage over what you've been given already (assuming it does what you want it to do!!).

So, over to you. Does my previous offering work, or not? If not, where? What should it give, and why?

[billbalint]

I neglected to note that each data point pulled over needs to be on the "InvoiceLines" Row on the new dataset.

I'm honestly hopeful that it's even possible. Somewhere in this project, the SKUs got completely scrambled and there is no matching dataset with old SKUs to do a simple lookup. What a mess!

It also, needs to leave a blank if there is no SKU, like in the instance that I've highlighted red.

[billbalint]

AliGW, If you see what I've just Attached to Glenn, I am using MS365 V2209 Build 15629.20208. Updated my profile to show.

[Glenn Kennedy]

Try:
=IF(K2="InvoiceLines",INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$36)/(($D$2:$D$36=L2)*($F$2:$F$36<>"")),COUNTIFS(K$2:K2,"InvoiceLines",L$2:L2,L2))),"")

[Glenn Kennedy]

I just noticed the bit about the red shading.

Ryan Floss has 7 entries on the left. One blank and 6 values.

On the right he has 6 ILs. You want 1 blank and 5 values returned.

BUT in the case of Ryan Pervola, 2 on the left... one blank and one value. HOWEVER, you want the value, not the blank returned.

EXPLAIN the logic and DEFINE the rule that governs this....

[billbalint]

My computer is choking but this looks to be working! You are the man, Glenn!

I've been getting a lot of Calculating (8 Threads):0% issues. I copied and pasted values after adjusting and copying the formula down to 165600 and it's at 57% after 5 minutes.

Any suggestions to help this error? I know there is something about cleaning the cache. I haven't used this computer or this version of excel very much, it's 5 months old.

[Glenn Kennedy]

Did you see post 11? Does that cause issues??

[Glenn Kennedy]

Is this any faster (assuming Post 11 isn't a problem)?

[Glenn Kennedy]

or this???

[billbalint]

Your initial IF="InvoiceLines" worked perfectly!

Thank you, Glenn. Can I add you as a friend? I have one of these really buggery ones once in a while, hence my short history on the forum. I really appreciate it. You saved me a huge headache!

[Glenn Kennedy]

Sure thing. I'm not sure what difference being a friend makes to either of us, but....

You're welcome & glad it worked.

[billbalint]

So I can remember you in case it is two years until I have an issue that I can't troubleshoot and figure. Thanks agian!

[Glenn Kennedy]

LoL. That makes sense!!