CODEWITHSUNDEEP
excelstringindexingexcel-formulapartial-matches
ChrisGila
ChrisGila

Reputation: 41

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

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.

enter image description here

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

Upvotes: 0

Views: 570

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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

enter image description here

• 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.

enter image description here

• Formula used in cell C3

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

Upvotes: 3

Related Questions