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

Copper Contributor

 

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 in column A.

Example:

Column A: 20160101|20151110|20150812|20150513|20150212|20141112|20140814|20140514|20140219

Column B: Cancelled|Active|Active|Active|Active|Active|Active|Active|Active

I need the value in column A that matches with the position where 'Cancelled' is present in column B. Both column with have equal values while column A can also have blanks.

IN this example column B has the word 'Cancelled' in first position so I'd like to fetch the value in first position from column A i.e. 20160101

 

Example 2:

Column A: 20160101|20151110|20150812

Column B: Active|Cancelled|Active

Cancelled in 2nd position in column B, I'd like to fetch value in 2nd position from column A i.e. 20151110

Example 3:

Column A: |20151110|20150812

Column B: Cancelled|Active|Active

Cancelled in 1st position in column B, I'd like to fetch value in 1st position from column A i.e. Blank

Example 4:

Column A: 20160101|20151110|20150812

Column B: Active|Cancelled|Cancelled

Cancelled in 2nd abd 3rd positions in column B, I'd like to fetch values in 2nd and 3rd position from column A i.e. 20151110 and 20150812

Can someone please help me out with this.

 

Regards,

Mahender

2 Replies

@mahib4u Interesting challenge! I used Data, From Table to solve this puzzle, see attached. Note that it seems to fail if any of the codes are non-numeric (or sort after the word "Cancel")

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