Excel formula - How to find pending text

[TH123]

Hi guys,

Sorry that I don't know how to upload under excel format so that pls help to review this image.
I'd like to get result in column C = "c" following below logic however don't know which excel formula is workable. Kindly help to solve issue. Thanks.

1. Requirement: a/b/c
2. Approved: a/b
3. Pending: c haven't been approved yet.

 

[Sufiyan97]

Try

Book2
	A	B	C	D
1	Requirement	Approved	Pending
2	A/b/c	a/b	c
3
4
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TEXTAFTER(LOWER(A2),LOWER(B2)&"/")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1	Requirement	Approved	Pending
2	a/b/c	a/b	c
3	a/b/c	c/a	b
4	c/a/b	c	a/b
Main

Cell Formulas
Range	Formula
C2:C4	C2	=LET(a,TEXTSPLIT(A2,"/"),TEXTJOIN("/",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(B2,"/"))))))

 

[TH123]

Try

Book2
	A	B	C	D
1	Requirement	Approved	Pending
2	A/b/c	a/b	c
3
4
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TEXTAFTER(LOWER(A2),LOWER(B2)&"/")

Hi Sufiyan,

Thanks for your support. Your suggestion is correct in case a/b or a is approved while I tried with b is approved then pending is shown c only.
Hope to get support on next posts.

 

[TH123]

Another option

Fluff.xlsm
	A	B	C
1	Requirement	Approved	Pending
2	a/b/c	a/b	c
3	a/b/c	c/a	b
4	c/a/b	c	a/b
Main

Cell Formulas
Range	Formula
C2:C4	C2	=LET(a,TEXTSPLIT(A2,"/"),TEXTJOIN("/",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(B2,"/"))))))

Hi Fluff,

If all are approved then can I insert with using IFERROR(your formula,"No pending")? Cause I tried with all are approved then formula show #CALC!

 

[Fluff]

Rather than using iferror, you can use

=LET(a,TEXTSPLIT(A2,"/"),TEXTJOIN("/",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(B2,"/"))),"No Pending")))

 

[TH123]

Rather than using iferror, you can use

=LET(a,TEXTSPLIT(A2,"/"),TEXTJOIN("/",,FILTER(a,ISNA(XMATCH(a,TEXTSPLIT(B2,"/"))),"No Pending")))

Thanks Fluff. Your formula woks well & this is what I need exactly.

 

[Fluff]

Glad we could help & thanks for the feedback.