Menu
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?
302
If you just want to find which name is partial match the given name, you also can use this formula =INDEX($E$2:$E$14,MATCH($K$1&"*",E2:E14,0)). (E2:E14 is the column list you want to lookup from, k1 is the given name, you can change as you need.)
To get the position of the first partial match (i.e. the cell that contains text you are looking for) you can use the MATCH function with wildcards. The MATCH function returns the position or "index" of the first match based on a lookup value in a range.
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
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
