Index, Match Formula HELP!!!

[illarmy]

Hello,

I am trying to modify a formula that I have working, but I need it to pull duplicate values for the same return value that is found. I have looked at other posts on this forum similar to my question, but it seems all them are not as complex as mine. I've seen some index/match formulas utilize countif, small, or offset, but I am unfamiliar with these and I'm not sure if they could even be useful in my scenario.

My Formula:

=IFERROR(INDEX(INDIRECT(Array_1),MATCH(B$9,INDIRECT(Array_2),0),1),IFERROR(INDEX(INDIRECT(Array_1),MATCH($B$9,INDIRECT(Array_3),0),1),IFERROR(INDEX(INDIRECT(Array_1),MATCH(B$9,INDIRECT(Array_4),0),1),IFERROR(INDEX(INDIRECT(Array_1),MATCH(B$9,INDIRECT(Array_5),0),1),"Not Found"))))

B9 IS THE VENDOR THAT'S ON THE VENDOR TAB (If the vendor is Bob's Burgers it would be found in one of the arrays listed below)

[TABLE="width: 228"]
<tbody>[TR]
[TD]Array_1[/TD]
[TD]January!$B$22:$S$200[/TD]
[/TR]
[TR]
[TD]Array_2[/TD]
[TD]January!$K$22:$K$200[/TD]
[/TR]
[TR]
[TD]Array_3[/TD]
[TD]January!$N$22:$N$200[/TD]
[/TR]
[TR]
[TD]Array_4[/TD]
[TD]January!$Q$22:$Q$200[/TD]
[/TR]
[TR]
[TD]Array_5[/TD]
[TD]January!$T$22:$T$200
[/TD]
[/TR]
</tbody>[/TABLE]

These arrays are looking at columns where the vendor names are placed.

The purpose of this formula is I am having it search through each of the array name boxes I've labeled to different columns to return a text value. The problem is it's returning the first one it comes across and there could be a scenario where there could be more than one value to return on a separate line.

So i have these Array_1 thru Array_5 pulling from a master date that when it changes to any month the formula updates to pull from a different tab labeled one of the months. For Example, if I want it to show May it would show: May!$B$22:$S$200.

For more detail, this is an invoice and I have services listed on this invoice. From there, there are vendors who receive a portion of the services billed (like a split). For example, I bill $3.00 for one service and x gets $1.00 and y gets $2.00 for every unit billed. In this scenario I could have a lot of x's and y's, but will not know until I bill the client for that month. I am trying to play out every scenario here. All services will be unique, but the vendors could be getting a split on multiple services billed.

To give more background of how the invoice is set up,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Service/Product[/TD]
[TD]Rate[/TD]
[TD]Units[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Split[/TD]
[TD]Vendor[/TD]
[TD]Rate[/TD]
[TD]Split[/TD]
[TD]Vendor[/TD]
[TD]Rate[/TD]
[TD]Split[/TD]
[TD]Vendor[/TD]
[TD]Rate[/TD]
[TD]Split[/TD]
[TD]Vendor[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]$6[/TD]
[TD]50[/TD]
[TD]$300[/TD]
[TD][/TD]
[TD]$150[/TD]
[TD]x[/TD]
[TD]$3[/TD]
[TD]$150[/TD]
[TD]y[/TD]
[TD]$3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adjustments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]$10[/TD]
[TD]50[/TD]
[TD]$550[/TD]
[TD][/TD]
[TD]$110[/TD]
[TD]x[/TD]
[TD]$2[/TD]
[TD]$220[/TD]
[TD]y[/TD]
[TD]$4[/TD]
[TD]$165[/TD]
[TD]z[/TD]
[TD]$3[/TD]
[TD]$55[/TD]
[TD]aa[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Adjustments[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So this all starts in B22, all invoices start there. So in this scenario vendor x is getting $5 total, my formula would only bring in the $3 line item and return Product1, I need it to also return Product2 as well. I have to do this for the rate and units as well, but I figure once I solve this I can copy the formula to adjust for the rate & units that I need to return as well. This formula is also pasted on a separate tab with that vendors name and this is where the formula will be kept.

So the reason I've split out the table horizontally, is since a service could be split multiple ways and my formula wouldn't be able to return the service/product as it would not correspond to the right row.

If I need more detail about this or if my instructions/explanation is unclear please let me know as I haven't been able to figure this one out. I apologize for the lengthy explanation but I've made the sheet so intertwined I feel if I don't give enough insight it may to difficult to interpret.

Thanks