Finding partial text in range return an index

Question

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

User · Answer

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

User · Answer

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     amp H1 amp     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  amp 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

User · Answer

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     amp B1 amp     and changed the search to  COUNTIF B1 B10      amp A1 amp       0  the extra column to allow finding the first of last entry in the concatenated string

[excel] Finding partial text in range, return an index

Examples related to excel