Forum Discussion

mahib4u's avatar
mahib4u
Copper Contributor
Jan 03, 2022

Find value in same position with specific word in next cell.

  Hi everyone, I've two cells(A and B) with values separated by pipes. For a specific word in column B I'd like to fetch the value from column A which is in the same position as the specific word i...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 04, 2022

mahib4u 

As variant

=LET(
   splitA, FILTERXML(  "<t><s>"&SUBSTITUTE(A2,"|","</s><s>")&"</s></t>", "//s" ),
   splitB, FILTERXML(  "<t><s>"&SUBSTITUTE(B2,"|","</s><s>")&"</s></t>", "//s" ),
   pos, SEQUENCE(ROWS(splitB))*(splitB="Cancelled"),
   f, FILTER(pos, pos),
   IFERROR( TEXTJOIN( ", ", 1, INDEX( splitA, f ) ), "" ) )

Resources