how to return a specific value if a substring (i.e., a word) is found in a cell which contains text in EXCEL

Question

I have the following two tables in EXCEL. I want to return the specific value (in the cells highlighted in yellow) from the Table 2. Table 2 has eight variables (i.e. substrings) horizontally and some other variables in Column H. I tried to use SEARCH with INDEX and MATCH with wildcards to return the values when the specific substring appears in the text (either at the beginning of the sentence or in between) from the column B called "Variable" of Table 1, but it did not work.

Table1

Variable	aaaa	bbbb	cccc
dff dfkjdfj Copper	1	14	27
jdfjfd Steel & Iron dfff	2	15	28
sddsd Aluminium	3	16	29
sdsdsd Zinc	5	18	31
dfdf Tin	6	19	32
dfdfd Nickel jkdhffdhdf	7	20	33

Table2

	Copper	Steel & Iron	Aluminium	Lead	Zinc	Tin	Nickel	Tantalum	lolybdenul	Wolfram	Silver	Gold	Platinum
aaaa	1	2	3	4	5	6	7	8	9	10	11	12	13
bbbb	14	15	16	17	18	19	20	21	22	23	24	25	26
cccc	27	28	29	30	31	32	33	34	35	36	37	38	39
dddd	40	41	42	43	44	45	46	47	48	49	50	51	52

Answer 1

You could try using the formula as shown in the screenshot:

---

• Formula used in cell C3

=INDEX($I$3:$U$6,MATCH(C$2,$H$3:$H$6,0),MATCH(TRUE,ISNUMBER(SEARCH(" "&$I$2:$U$2&" "," "&$B3&" ")),0))

---

Another probable alternative could be using XLOOKUP() with COUNTIF() & FILTER() Function.

Since the first alternative is an array formula, based on your Excel version needs to hit CTRL+SHIFT+ENTER while exiting the edit mode, however if you are using O365 or Excel 2021 you dont require.

---

---

• Formula used in cell C3

=XLOOKUP(
    1,
    COUNTIF(
        $B3,
        "*" & $I$2:$U$2 & "*"
    ),
    FILTER(
        $I$3:$U$6,
        C$2 = $H$3:$H$6
    )
)

---