27

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.

egzample

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Endriu
  • 329
  • 1
  • 4
  • 8

3 Answers3

38

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Your method is working too, THX :) But have a question... What should i do to search in multiple column range? ( not in G:G range but for example A:G range ?) – Endriu Mar 22 '13 at 15:54
  • I edited my answer to give a solution for multiple columns - if there is more than one match it takes the one in the lowest row.... – barry houdini Mar 22 '13 at 16:36
  • Real Houdini ;) ITS WORKING :) – Endriu Mar 22 '13 at 17:37
  • Thanks, save me a lot of time. If need to get the data of different column at the same row, the function is like this =INDEX(B:B;MATCH("string";A:A;0)) – zhihong Sep 12 '14 at 08:45
  • Thanks! By the way, if the number of the characters in the cell we search greater than 256, refer to this post https://stackoverflow.com/questions/20503745/excel-match-character-limit – bfhaha Sep 08 '17 at 17:22
12

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.

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
3

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.

Mark Cramer
  • 31
  • 1