Multiple Return Values

[billbalint33]

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.

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

Thank you for your help!!!

 

[Peter_SSs]

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’)

You might also investigate the following so that helpers are not put off by having to type out large amounts of sample data to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[gifariz]

How about?
1. Insert new column before PRODUCT
2. P2=IF(N2<>N1,1,P1+1)
3. Q2=INDEX(FILTER(G:G,A:A=N2),P2)
4. Auto-fill column P & Q for rest of data

Assuming Invoice Number and OrderID are the reference

 

[Legacy 143009]

Here is an old school alternative. P2:

=IFERROR(INDEX($G$2:$G$1000,SMALL(IF(($D$2:$D$1000=M2)*($A$2:$A$1000=N2),MATCH(ROW($G$2:$G$1000),ROW($G$2:$G$1000))),ROWS($A$1:A1))),"")

This is an array formula. Just hitting Enter is not enough.
Paste the formula and press Ctrl+Shift+Enter together.

 

[Automatrix]

An Outer merge in Power Query should give you all the combinations.

 

[Legacy 143009]

Here is an old school alternative. P2:

=IFERROR(INDEX($G$2:$G$1000,SMALL(IF(($D$2:$D$1000=M2)*($A$2:$A$1000=N2),MATCH(ROW($G$2:$G$1000),ROW($G$2:$G$1000))),ROWS($A$1:A1))),"")

This is an array formula. Just hitting Enter is not enough.
Paste the formula and press Ctrl+Shift+Enter together.

A small correction:

=IFERROR(INDEX($G$2:$G$1000,SMALL(IF((($D$2:$D$1000=M2)*($A$2:$A$1000=N2))+1,MATCH(ROW($G$2:$G$1000),ROW($G$2:$G$1000))),ROWS($A$1:A1))),"")