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)
<tbody>
</tbody>
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,
<tbody>
</tbody>
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
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)
| Array_1 | January!$B$22:$S$200 |
| Array_2 | January!$K$22:$K$200 |
| Array_3 | January!$N$22:$N$200 |
| Array_4 | January!$Q$22:$Q$200 |
| Array_5 | January!$T$22:$T$200 |
<tbody>
</tbody>
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,
| Service/Product | Rate | Units | Total | Split | Vendor | Rate | Split | Vendor | Rate | Split | Vendor | Rate | Split | Vendor | Rate | |
| Product1 | $6 | 50 | $300 | $150 | x | $3 | $150 | y | $3 | |||||||
| Adjustments | ||||||||||||||||
| Product2 | $10 | 50 | $550 | $110 | x | $2 | $220 | y | $4 | $165 | z | $3 | $55 | aa | $1 | |
| Adjustments | 5 | |||||||||||||||
<tbody>
</tbody>
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
