Excel matching 2 columns by text and numbers

user_11894368 0
Viewed 48

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: enter image description here

Table2 data:

table2

Expected result should look like this: result 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 itemid
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!

vlookup formula match excel-formula excel
1 Answers

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

enter image description here

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.

user_97581940
Related