Reputation: 5550
I would like to find a formula solution, which takes 2 parameters: a table and a text, and returns the cell in the table that contains that text as substring.
For instance,
im
, I expect the formula to return Cell B2, whose value is Jim
.omas
, I expect the formula to return Cell C5, whose value is Thomas
.We could assume that there is only one cell in the table satisfying this condition. For anyone wants to go further, when there are several cells in the table matching the condition, we could try to return the topleft one.
Ideally,
:
to build a range, for instance ...:F10
.Does anyone know how to achieve this?
Upvotes: 2
Views: 1330
Reputation: 1338
Two possibilities:
a) If you are looking for a solution that shows all matches, you could use this beatiful formula :)
=IFERROR(INDEX($A$1:$D$5,
AGGREGATE(15,6,ROW($A$1:$A$5)/(SEARCH($G$1,$A$1:$D$5)>0),ROW(A1)),
AGGREGATE(15,6,COLUMN($A$1:$D$1)/((SEQUENCE(ROWS($A$1:$A$5),COLUMNS($A$1:$D$1),1,1)/AGGREGATE(15,6,(SEQUENCE(ROWS($A$1:$A$5),COLUMNS($A$1:$D$1),1,1)*(SEARCH($G$1,$A$1:$D$5)>0)),ROW(A1)))=1),1))
,"-")
You "just" have to change the ranges according to your data. :)
b) If you are fine with just the first match, then you can use
=IFERROR(INDEX($A$1:$D$5,
AGGREGATE(15,6,ROW($A$1:$A$5)/(SEARCH($G$1,$A$1:$D$5)>0),1),
AGGREGATE(15,6,COLUMN($A$1:$D$5)/(SEARCH($G$1,$A$1:$D$5)>0),1)),
"-")
which doesn't fail if there are multiple possible matches. If you want to know how many matches there are, then just simply use
=COUNTIF(A1:D5,"*"&G1&"*")
Upvotes: 2
Reputation: 3490
Generate a matrix of matches with SEARCH against the condition and the input table. Find the first column with a match, and the first row in that column with a match, then pass those numbers to INDEX. Using LET to make it easier to read:
=LET(
t, R1C1:R5C3,
cond, "*" & RC5 & "*",
matches, IFERROR(SEARCH(cond, t), 9999),
col, MIN(matches*COLUMN(t)),
rw, MIN(INDEX(matches*ROW(t), FALSE, col)),
INDEX(t, rw, col)
)
Since a cell reference automatically evaluates to the cell's value, I've put a second formula in col 7 to just dump the reference string.
Definitions: t is the input table. matches is the array of matches. It would be either 1 or error, so I wrap in IFERROR, and since I'm going to get the MIN later, I use an arbitrarily high number so that my array is either 1 or 9999. col uses MIN to find the lowest matching column, then I use INDEX to return that column from the input table (t) and get the minimum row. Combining those numbers gets me a string I can use in INDIRECT.
Notice that you can chain this formula with : to produce a range:
Upvotes: 2
Reputation: 11653
=INDEX(A:D,SUMPRODUCT(ISNUMBER(SEARCH("?"&"im",B2:D5))*(ROW(B2:D5))),SUMPRODUCT(ISNUMBER(SEARCH("?"&"im",B2:D5))*(COLUMN(B2:D5))))
Be careful in using this, since multiple matches will return an error or wrong result!
You may also consider not using "?"&
in front of both searches, but this will include matches that start with the search value.
Upvotes: 1