I need to find a partial text in a specific range and get a value which is X rows under cell index of found text. I have tried with INDEX and MATCH functions but without success.
As in example: looking for a partial of text ASDFGHJK and need returned the value three rows under: (I NEED THIS VALUE). I have tried to get a row index of found cell and +3 to get needed value ("I NEED THIS VALUE") but without success.
Any ideas how to do this?
This question is related to
excel
The answer is
You can use "wildcards" with MATCH so assuming "ASDFGHJK" in H1 as per Peter's reply you can use this regular formula
=INDEX(G:G,MATCH("*"&H1&"*",G:G,0)+3)
MATCH can only reference a single column or row so if you want to search 6 columns you either have to set up a formula with 6 MATCH functions or change to another approach - try this "array formula", assuming search data in A2:G100
=INDIRECT("R"&REPLACE(TEXT(MIN(IF(ISNUMBER(SEARCH(H1,A2:G100)),(ROW(A2:G100)+3)*1000+COLUMN(A2:G100))),"000000"),4,0,"C"),FALSE)
confirmed with Ctrl-Shift-Enter
I just found this when googling to solve the same problem, and had to make a minor change to the solution to make it work in my situation, as I had 2 similar substrings, "Sun" and "Sunstruck" to search for. The offered solution was locating the wrong entry when searching for "Sun". Data in column B
I added another column C, formulaes C1=" "&B1&" " and changed the search to =COUNTIF(B1:B10,"* "&A1&" *")>0, the extra column to allow finding the first of last entry in the concatenated string.
This formula will do the job:
=INDEX(G:G,MATCH(FALSE,ISERROR(SEARCH(H1,G:G)),0)+3)
you need to enter it as an array formula, i.e. press Ctrl-Shift-Enter. It assumes that the substring you're searching for is in cell H1.
Source: Stackoverflow.com