Find value in range considering multiple criteria

[phmalu]

Hi all!

I'm facing a small problem to find values in this table considering multiple criteria.
The challenge is to fill values in J-M (just like I did manually) but using a formula instead. In order to do so I have to match the value in A for the row with the one in F (or G,H,I) and then find it somewhere in D or E. From there, the answer will be 2 columns left (B or C) and it is in the exact same row as the value in D or E. If the value is duplicate, the 1st entry in F-I has to match the first in D-E and so on (e.g. duplicates - F1 matches D4; G2 matches E4).

Note: Use value in F to find J; G to K; H to L...

Another exemple:
-Finding what to write in L6:
1. Because H matches L (and same row), first find H6 in D-E.
2. But don't forget the second criteria! Values in A have to be the same for all valid rows... hence, we can be sure H6 will be in D6:E7.
This is important because values in D-E are not unique and if A is not taken into account then it may give the wrong answer (e.g E2 and E7)
2. Finding H6 e in D6:E7.... H6 = D7
3. From D7, we know all answers will be in the same row but 2 columns to the left (fixed rule valid for the entire table): Value in B7 is the matching one (final answer). Hence, L6=B7

I hope this is enough for you all to understand the logic behind what I want. But be free to ask me for more details!
Ty you all in advance!
-P

VSF_test_070522.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	NO.	CF1	CF2	CV1	CV2	S_CV1	S_CV2	S_CV3	S_CV4	VFS_1	VFS_2	VFS_3	VFS_4
2	1	36	55	1538	247	139	139	247	1538	49	49	55	36
3	1	28	26	3927	4721
4	1	49	49	139	139
5	1	70	71	4798	5360
6	2	50	42	161	178	161	178	227	247	50	42	41	51
7	2	41	51	227	247
8	3	38	43	62	73	62	73	119	1021	38	43	44	31
9	3	31	32	1021	79
10	3	44	46	119	30
VSF_test

 

[Peter_SSs]

Try this in J2, copied across and down

22 07 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	NO.	CF1	CF2	CV1	CV2	S_CV1	S_CV2	S_CV3	S_CV4	VFS_1	VFS_2	VFS_3	VFS_4
2	1	36	55	1538	247	139	139	247	1538	49	49	55	36
3	1	28	26	3927	4721
4	1	49	49	139	139
5	1	70	71	4798	5360
6	2	50	42	161	178	161	178	227	247	50	42	41	51
7	2	41	51	227	247
8	3	38	43	62	73	62	73	119	1021	38	43	44	31
9	3	31	32	1021	79
10	3	44	46	119	30
Find value

Cell Formulas
Range	Formula
J2:M10	J2	=IFERROR(INDEX(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF($A$2:$A$10=$A2,IF($D$2:$E$10=F2,$B$2:$C$10,""),""))&"</c></p>","//c"),COUNTIF($F2:F2,F2)),"")

 

[phmalu]

Try this in J2, copied across and down

22 07 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	NO.	CF1	CF2	CV1	CV2	S_CV1	S_CV2	S_CV3	S_CV4	VFS_1	VFS_2	VFS_3	VFS_4
2	1	36	55	1538	247	139	139	247	1538	49	49	55	36
3	1	28	26	3927	4721
4	1	49	49	139	139
5	1	70	71	4798	5360
6	2	50	42	161	178	161	178	227	247	50	42	41	51
7	2	41	51	227	247
8	3	38	43	62	73	62	73	119	1021	38	43	44	31
9	3	31	32	1021	79
10	3	44	46	119	30
Find value

Cell Formulas
Range	Formula
J2:M10	J2	=IFERROR(INDEX(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF($A$2:$A$10=$A2,IF($D$2:$E$10=F2,$B$2:$C$10,""),""))&"</c></p>","//c"),COUNTIF($F2:F2,F2)),"")

Apparently it works just the way it should, thank you so much for your help!
I'm just wondering, is there any alternative solution without having to CSE? If it's going to be extra laborious, I don't need though.
Thank you!

 

[Peter_SSs]

is there any alternative solution without having to CSE?

I don't have 2019 to test with but I thought that you might not need CSE with that version. In any case, whether you have to press CSE or not, the formula must actually process an array of values since that is what you are dealing with in each case.

If the sheet is 'sluggish' because of the formulas you could see if this makes a difference.

=IF(F2="","",IFERROR(INDEX(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF($A$2:$A$10=$A2,IF($D$2:$E$10=F2,$B$2:$C$10,""),""))&"</c></p>","//c"),COUNTIF($F2:F2,F2)),""))