Search code examples
excelstringfunctionexcel-formulawords

Finding a Specific Element Inside Other Formulas

I need help writing a function to find "Th+4" and ignore all other attached chemical elements.

Example of the data; 

/HASr+(aq)  1.2595E-12
Sr+2    2.9449E-06
SrCl+   1.4637E-10
SrCO3 (aq)  1.01E-10
SrF+    2.1778E-11
SrHCO3+ 3.2969E-09

How can I find only Sr+2 and ignore all the others? For now, I was using this function to find it, but it displays all elements containing Sr2+.

=IF(ISERROR(FIND("Sr+2",A7,1)),B7,"")

Please help, Thank you !

Solution

  • Your question is very vague, but I assume that when you "locate" your value, you want to return the value to the right.

    You can use Index() and Match() to do this for you.

    Assuming that your lookup range is column A, and the value to return is column B, this should work: 

    =INDEX(A:B,MATCH("Sr+2", A:A),2)

    enter image description here

    Breaking Down the Formula

    Index()
    - A:B is the entire range to index. Your lookup value is column A, and your return value is column B
    - Match() is returning the row number, which we will show that below.
    - 2 is column to cross reference the return value of the row from Match(). Since Match() gives us the row #, this gives us the column number to "pin-point" the return value

    Match()
    - "Sr+2" is the string to be searched for
    - A:A is the location to search for this string.
    This returns the row to your Index() function