SoftTimur
SoftTimur

Reputation: 5550

Find the cell matching a condition in a table

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,

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,

Does anyone know how to achieve this?

enter image description here

Upvotes: 2

Views: 1330

Answers (3)

OverflowStacker
OverflowStacker

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))
    ,"-")

enter image description here

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

Chris Strickland
Chris Strickland

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.

enter image description here

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:

enter image description here

Upvotes: 2

P.b
P.b

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

Related Questions