How to show relative value in searched values?

[Fractalis]

Hi all,

I want to search within range A1:B16 the CODE for each NUM in D1 to D8 with one condition.

There are some CODEs that are of the form XXXXZZ and XXXX24 that belongs to the same "group". The CODE that ends with 2 numbers is the "MAIN" CODE within than group.

The "MAIN" CODES begins with 333391.

For example, XNUMTE and XNUM49 belongs to same group and main is XNUM49. Following this example, depending the numbers searched, the formula matches XNUMTE or XNUM49.

In E2:E8 the output is correct, but when in output there is a MAIN CODE, I'd like to show MAIN value instead the other CODE for that group like in output H2:H8.

How can I do this in Excel 2019?

I hope make sense. Thanks

file.xlsx
	A	B	C	D	E	F	G	H
1	CODE	NUM		NUM	CODE		NUM	CODE
2	ACGUSG	1312314		7965	SBDRUD		7965	SBDRUD
3	ACGUSG	1404725		120631	DLTNLT		120631	DLTN75
4	DLTNLT	120631		33339145	DLTN75		33339145	DLTN75
5	DLTN75	33339145		1404725	ACGUSG		1404725	ACGUSG
6	DLTNLT	316540		5275497	XNUMTE		5275497	XNUM49
7	DLTNLT	3460643		233663	XNUMTE		233663	XNUM49
8	SBDRUD	7965		33339100	XNUM49		33339100	XNUM49
9	SBDR12	796660
10	SBDRUD	33339183
11	SBDRUD	7967
12	XNUMTE	5199799
13	XNUMTE	233663
14	XNUM49	33339100
15	XNUMTE	52551223
16	XNUMTE	5275497
Sheet12

Cell Formulas
Range	Formula
E2:E8	E2	=INDEX($A$1:$A$16,MATCH(D2&"*",$B$1:$B$16,0))

 

[bebo021999]

Book3
	A	B	C	D	E
1	CODE	NUM		NUM	CODE
2	ACGUSG	1312314		7965	SBDR12
3	ACGUSG	1404725		120631	DLTN75
4	DLTNLT	120631		33339145	DLTN75
5	DLTN75	33339145		1404725	ACGUSG
6	DLTNLT	316540		5275497	XNUM49
7	DLTNLT	3460643		233663	XNUM49
8	SBDRUD	7965		33339100	XNUM49
9	SBDR12	796660
10	SBDRUD	33339183
11	SBDRUD	7967
12	XNUMTE	5199799
13	XNUMTE	233663
14	XNUM49	33339100
15	XNUMTE	52551223
16	XNUMTE	5275497
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(LOOKUP(2,1/(LEFT($A$2:$A$16,4)=LEFT(INDEX($A$2:$A$16,MATCH(D2,$B$2:$B$16,0)),4))/ISNUMBER(RIGHT($A$2:$A$16,2)+0),$A$2:$A$16),INDEX($A$2:$A$16,MATCH(D2,$B$2:$B$16,0)))

 

[Fractalis]

Thanks for your answer. It almost work, but for some reason the first value shows SBDR12 instead of SBDRUD.