SOLVED

Return Common Row Number if Conditions Are Matched

Brass Contributor

Hello There,

 

My attached image is self explanatory.

I attach raw file too.

 

TIA

5 Replies
best response confirmed by mohsin91santono-_-1220 (Brass Contributor)
Solution

Hi @mohsin91santono-_-1220 ,

 

It could be

=IFERROR(MATCH(1,INDEX(ISNUMBER(SEARCH($J$14,$A:$A))*($H:$H=$K$14),0),0),"no such")

Please see in attached file.

 

The formula in L14 is:
=MAX(INDEX(ROW(A2:A5)*
ISNUMBER(FIND(J14,A2:A5))*
COUNTIF(K14,H2:H5),0))
This array formula (enter formula using control+shift+enter) does the trick:
=MATCH("*"&J14&"*|"&K14,$A$2:$A$5&"|"&$H$2:$H$5,0)
Note the * wildcard characters enabling the code to be found in any part of the cells of column A
Hi Sergei
Salute!!
You are awesome :D
1 best response

Accepted Solutions
best response confirmed by mohsin91santono-_-1220 (Brass Contributor)
Solution

Hi @mohsin91santono-_-1220 ,

 

It could be

=IFERROR(MATCH(1,INDEX(ISNUMBER(SEARCH($J$14,$A:$A))*($H:$H=$K$14),0),0),"no such")

Please see in attached file.

 

View solution in original post