Just need a tweak on countif formula....due to space

[SuzyQPA2]

Excel Workbook
	A	B	C	D
1	Plan Per IBX	Plan per system	Match Plan	*
2	DIRECTPOS	KEYSTONE DIRECT POS FT	FALSE	not working
3	HMO HIGH	KEYSTONE HMO HIGH FT	TRUE	working
Sheet2

 

[Haseeb Avarakkan]

Hello, Try any of these,

=OR(AND(LEFT(RIGHT(A1,2))="F",LEFT(B1)="F"),AND(LEFT(RIGHT(A1,2))="P",LEFT(B1)="P"))

=OR(AND(RIGHT(A1,2)="FT",ISNUMBER(SEARCH("full*time",B1))),AND(RIGHT(A1,2)="PT",ISNUMBER(SEARCH("part*time",B1))))

 

[Scott Huish]

Good idea with the wildcard and search, could be shortened to:

=ISNUMBER(SEARCH(IF(RIGHT(A1,2)="FT","full*time","part*time"),B1))

 

[SuzyQPA2]

The information is coming from different systems.
I have the Plan information they are enrolled in which represents COL A
and I have the EE Payroll Status coming from Payroll which represents COL B
so if the payroll person entered things correctly they will always be a match but they will be worded as you can see differently.
I actually resolved this the long way by inserting another column and this formula to get
an FT or PT since I couldn't get a formula to work. A long way around the problem.
I will look at your formula and see if I can replace my long way w/a shorter way.

=IF(OR(b2="fulltimeregular",b2="fulltimetemporary",b2="fulltime-regular",b2="fulltime-temporary"),"FT","PT")

Then i compare same to same with another formula
=COUNTIF(A2,"*"&C2&"*")>0
This is looking for the FT or PT in COL A and if it's a match gives me a true/false

 

[SuzyQPA2]

Thanks Haseeb & Hotpepper - I will try out this shorter version will save me spreadsheet space and work.

 

[SuzyQPA2]

Excel Workbook
	A	B	C	D
1	plan	EE status var
2	KEYSTONE POS FT	Fulltime-Regular	TRUE
3	HMO HIGH FT- KEYSTONE	Fulltime-Regular	FALSE	can your formula allow for this being in the middle?
4	KEYSTONE HMO LOW FT	Fulltime-Regular	TRUE
5	KEYSTONE HMO LOW FT	Fulltime-Regular	TRUE
6	KEYSTONE HMO LOW FT	Fulltime-Regular	TRUE
7	KEYSTONE POS PT	Parttime-Regular	TRUE
8	KEYSTONE POS FT	Parttime-Regular	FALSE
9	KEYSTONE POS FT	FulltimeRegular	TRUE
10	KEYSTONE HMO LOW FT	FulltimeRegular	TRUE
11	AMERIHEALTH HMO HIGH FT	Fulltime-Regular	TRUE
12	AMERIHEALTH HMO HIGH FT	Fulltime-Temporary	TRUE
Sheet2

 

[Scott Huish]

Would it always be before a hyphen if it were in the middle and would there be any other hyphens in the text?