Using sumproduct with left and right

[LuGyver]

The following task seems simple... I just hope it is possible
I will break this down into pieces to see each step because it boggles my head when I try to build a formula all the steps at once.
NOTE: it is okay if the steps have to be done in separate ranges. However I want to keep them in rows and not columns.

First data is in over one thousand rows, but to keep it simple, I say data is in A1:Y1 range.
Second, the set of single Digits in A2:E2 are changed manually.
Lastly, I would like the sumproduct formula done all in one row (ie A3:Y3) if it can be. However, I am splitting the steps to include A4:Y4 and A5:Y5 for simplicity in seeing what I am after....

1. In the range A1:Y1 I have pairs such as 12 23 13 34 72 43 95 63 48 17 etc.

2. in a second range, say A2:E2, I have single digit(s), which I enter as needed, such as 1 5 7 3 6

I would like to have sumproduct() look at the right of all the pairs in A1:Y1 and return all that match any of the digits in Range A2:E2.

In this case sumproduct in range A3:Y3 returns 23 13 43 63 95 17.

3. in range A4:Y4 I need sumproduct to look at the resulting pairs left digits, and return all the pairs the have their left digit = to the left digit(s) of the pairs results in A3:Y3..

In the case above, we would see the left digit of our original return from A3:Y3 which is = 23 13 43 63 95 17:
so those left digits are 23 = 2, 13 = 1, 43 = 4, 63 = 6, 95 = 9, 17 = 1 ------ (2, 1, 4, 6, 9, 1)

so we are now looking for any pairs in A1:Y1
that have left digits = 2, 1, 4, 6, 9, 1

4. The results in A4:Y4 would be 23 13 43 63 95 17 minus(-) the first results (from A3:Y3) = 48 (and any other pairs which arise). These results stay and will remain for the remained of the calculation AND include the following.

5. In A5:Y5, sumproduct needs to look at the results in A4:Y4 and bring any pairs from A1:Y1 that have the left and/or right digits from A4:Y4. in this case = 48 = digits 4, 8
so sumproduct will be looking for left or right digits from A1:Y1 which are = 4, 8 and then return 43, 34 and include 48

The final results are 43, 34, 48

This seems easy but I don't understand sumproduct enough yet. I have been using if() with countif(s) which is not efficient and gets real confusingly cumbersome.

Hope this is said well enough
Thank you for your time and help

 

[LuGyver]

SWEET! You just made my year

=IFERROR(INDEX($O3:$CR3,SMALL(IF(MMULT(COLUMN($CT3:$CX3)^0,IF(RIGHT($O3:$CR3,1)=TRANSPOSE($CT3:$CX3&""),1,0))*($O3:$CR3<>""),COLUMN($O3:$CR3)-COLUMN($O3)+1),COLUMNS($CZ3:CZ3))),"")

Perfectly done!

Thank you so much.

 

[LuGyver]

I haven't exhaustively tested the formulas, but I found an issue with the first one, and it appears the same issue occurs in all the formulas. The CZ3 formula should be:

=IFERROR(INDEX($O3:$CR3,SMALL(IF(MMULT(COLUMN($CT3:$CX3)^0,IF(RIGHT($O3:$CR3,1)=TRANSPOSE($CT3:$CX3&""),1,0))*($O3:$CR3<>""),COLUMN($O3:$CR3)-COLUMN($O3)+1),COLUMNS($CZ3:CZ3))),"")

Add the part in red. That section generates an offset to give to the first INDEX in the formula. The part in red is not needed if the starting column is A, but when you moved the ranges, it became necessary. The part in blue you can change if you want. I generally like the range there to be the first cell in the range, but it'll work as is.

I haven't tested the rest of the formulas for accuracy, but when I add the red part to the formulas in GK3, JV3, NG3, QR3, UC3, and XN3 they all start generating results.

Hi Eric W.
It's been a long time since you helped me on this. I have used this formula extensively and truly happy.
I am in need of a revisit, in that you are familiar withits performance. I am trying to do the same with multiple arrays but not to have the right or left function.

Here's the original and works great:
=IFERROR(INDEX($O3:$CR3,SMALL(IF(MMULT(COLUMN($CT3:$CX3)^0,IF(RIGHT($O3:$CR3,1)=TRANSPOSE($CT3:$CX3&""),1,0))*($O3:$CR3<>""),COLUMN($O3:$CR3)-COLUMN($O3)+1),COLUMNS($CZ3:CZ3))),"")

I really like how the original formula works and its functionality when I fill right.
My question:
Is it possible to accomplish this same idea with two arrays and not include the left/right section?

To be clear, I'm actually trying to do the same thing with two different arrays and simply display those that match in $O3:CR7 with $CZ3:CZ7 (if any)..., and will have no empty/blank cells between the results when I fill right.
Thank you for any help and if I need to post in a new thread please let me know.

 

[Eric W]

Due to how old this thread is, why don't you open a new one? I vaguely remember this, but it would take me a bit to entirely recreate the logic. Also, when you do, consider downloading and using the XL2BB tool, which allows you to show a sample of your sheet easily. And update your profile to show which version of Excel you're using. The newer versions of Excel have some new functions that could simplify this. I'll watch for the new thread.

 

[LuGyver]

Due to how old this thread is, why don't you open a new one? I vaguely remember this, but it would take me a bit to entirely recreate the logic. Also, when you do, consider downloading and using the XL2BB tool, which allows you to show a sample of your sheet easily. And update your profile to show which version of Excel you're using. The newer versions of Excel have some new functions that could simplify this. I'll watch for the new thread.

Thread 1156687 Here ya go! Took me a bit to figure out XL2BB tool