Compare Cell A to see if value is either in one column or both

[topswim]

I am to looking at value in A column and see if it's in only column B, but not in column C. If it's true (in column B only) then I want to enter cell A value in column D otherwise blank

A B C D (desired result from above)

Tre	rtp	plr	Tre
plr	plr	plr	blank
oki	tre	oki	oki
lpo	mng	fre	blank

 

[topswim]

Sorry. Let me clarify a little better as I needed to add one more column (see below). Also, it is not case sensitive so should not be relevant JEC.

Thank you for the responses though

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Tre	rtp	plr	Tre
3	plr	plr	plr
4	oki	tre	oki		oki
5	lpo	mng	fre
Main

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(COUNTIFS($B$2:$B$5,A2),COUNTIFS($C$2:$C$5,A2)=0),A2,"")
E2:E5	E2	=IF(AND(COUNTIFS($B$2:$B$5,A2)=0,COUNTIFS($C$2:$C$5,A2)),A2,"")

 

[Joe4]

And to follow up on my original post, in light of the recent update, this would go in cell D2:

=IF(AND(COUNTIF(B:B,A2)>0,COUNTIF(C:C,A2)=0),A2,"")

and copy down.

And this would go in cell E2:

=IF(AND(COUNTIF(B:B,A2)=0,COUNTIF(C:C,A2)>0),A2,"")

and copy down.

 

[topswim]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Tre	rtp	plr	Tre
3	plr	plr	plr
4	oki	tre	oki		oki
5	lpo	mng	fre
Main

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(COUNTIFS($B$2:$B$5,A2),COUNTIFS($C$2:$C$5,A2)=0),A2,"")
E2:E5	E2	=IF(AND(COUNTIFS($B$2:$B$5,A2)=0,COUNTIFS($C$2:$C$5,A2)),A2,"")

Thank you again Fluff. That worked

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[topswim]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Tre	rtp	plr	Tre
3	plr	plr	plr
4	oki	tre	oki		oki
5	lpo	mng	fre
Main

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(COUNTIFS($B$2:$B$5,A2),COUNTIFS($C$2:$C$5,A2)=0),A2,"")
E2:E5	E2	=IF(AND(COUNTIFS($B$2:$B$5,A2)=0,COUNTIFS($C$2:$C$5,A2)),A2,"")

Quick question. What if one of the cells is blank in a column? I am seeing I have some that are in column B, but column C is blank

 

[Fluff]

Blank cells shouldn't make a difference.

 

[topswim]

Blank cells shouldn't make a difference.

I didn't think so either, but when B is populated and C is not, B value is not coming back

 

[Fluff]

In that case can you post some sample data that shows the problem?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.