Search code examples
if-statementgoogle-sheetsgoogle-sheets-formula

Function to return value if a string contains similar values

I have data in two sheets that contain different string values for the same entity. For example, in Sheet A, a string is entered as "Biology" in A1, but in Sheet B it is entered as "Biology Course".

My goal is to find if the string in Sheet A is located in Sheet B, and then return the value that is in the cell below the string.

In other words if Sheet A looks like this:

Biology Blank

And Sheet B looks like this:

Biology Course Blank
200
300

Then I would like to return the value "200" in Sheet A. Thank you!

Solution

  • Give a try on-

    =INDEX(SheetB!A:A,MATCH("*"&A1&"*",SheetB!A:A,0)+1)