Excel matching 2 columns by text and numbers-CodePudding

I have 2 excel tables with data and i want from table 1 to get the item number and add it to table 2 based on item description thats contained in table 1 column itemName2.

Table1 data:

Table2 data:

Expected result should look like this: last column added based on formula that matches text from itemName2 in both tables, but not exact match - text in table2 columns is partly in itemName2 from table 1

Markdown data: table1

Location	ItemName2	Height	Width
7-04-01-A	U 104 ST9	2	23
7-04-01-B	U 708 ST9	2	23
7-04-01-C	H 1145 ST10	2	23
7-04-01-D	H 1733 ST9	2	23
7-04-02-A	U 108 ST9	2	23
7-04-02-B	U 727 ST9	2	23
7-04-02-C	H 1146 ST10	2	23
7-04-02-D	H 2033 ST10	2	23
7-04-12-C	H 1277 ST9	2	23

table2

24744/1277	ABS Кант EGGER	H 1277 ST9 акация лейкленд све	0,8	23	75000
24722/1277	ABS Кант EGGER	H 1277 ST9 акация лейкленд све	2,0	23	75000
24550/1312	43мм.ABS Кант EGGER	H 1312 ST10 Дъб Уайт. Пяс. Беж	2,0	43	75000
24558/1312	ABS кант EGGER	H 1312 ST10 Дъб Уайт. Пяс. Беж	2,0	23	75000
24565/1312	ABS кант EGGER	H 1312 ST10 Дъб Уайт. Пяс. Беж	0,8	23	75000
24550/1313	43мм.ABS Кант EGGER	H 1313 ST10 Дъб Уайтр. Сивокаф	2,0	43	75000
24558/1313	ABS кант EGGER	H 1313 ST10 Дъб Уайтр. Сивокаф	2,0	23	75000

Thank you in advance!

CodePudding user response：

I tried to understand the query as best as possible but I still see little to none resemblences. Either way, try the following:

Formula in E2:

=XLOOKUP(B2&" *",I$1:I$7,G$1:G$7,"",2)

Or, a classic INDEX() MATCH() combo:

=IFERROR(INDEX(G$1:G$7,MATCH(B2&" *",I$1:I$7,0)),"")

As you can see, the only match was generated on row 10 with the current sample data.