Workable Formula within Excel Table

[overmyer24]

Good evening - I'm struggling at trying to create a formula within a Table of data, which is driving a Pipeline Dashboard for our sales team.

Opportunity Owner	Opportunity Effective Date	Stage	Subscribers	Members	Funding
Sales A	1/1/2020	Finalist	10000	20000	FI
Sales B	1/1/2020	Closed Won	269	511	FI
Sales B	1/1/2020	Submitted to Customer	107	214	FI
Sales C	1/1/2020	Quoting	7020	13338	ASO
Sales D	1/1/2020	Initiated	4625	9250	ASO
Sales A	1/1/2020	Closed Lost	3294	6259	ASO
Sales A	1/1/2020	Closed Won	320	600	ASO

I didn't find anywhere to attach a spreadsheet, so I must've missed it. Anyway, I want a column next to "Funding" to be a formula that shows "Active" or "Inactive". Active would be associated with Finalist, Submitted, Quoting and Initiated. All other names within the Stage header would be Inactive. Can anyone quickly put together a formula for this?

Thanks,

rO

 

[Yongle]

Result below uploaded using XL2BB

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

I have assumed that you are using a structured Excel Table - if not replace structured references in dormula with cell references

Book1
	A	B	C	D	E	F	G
1	Opportunity Owner	Opportunity Effective Date	Stage	Subscribers	Members	Funding	Active
2	Sales A	01/01/2020	Finalist	10000	20000	FI	Active
3	Sales B	01/01/2020	Closed Won	269	511	FI	Inactive
4	Sales B	01/01/2020	Submitted to Customer	107	214	FI	Inactive
5	Sales C	01/01/2020	Quoting	7020	13338	ASO	Active
6	Sales D	01/01/2020	Initiated	4625	9250	ASO	Active
7	Sales A	01/01/2020	Closed Lost	3294	6259	ASO	Inactive
8	Sales A	01/01/2020	Closed Won	320	600	ASO	Inactive
Sheet7

Cell Formulas
Range	Formula
G2:G8	G2	=IF(OR([@Stage]="Finalist",[@Stage]="Submitted",[@Stage]="Quoting",[@Stage]="Initiated"),"Active","Inactive")

 

[overmyer24]

Thank you very much!!! I was adding too much information. Thank you again, appreciate it.!

 

[Peter_SSs]

Another way

overmyer24.xlsm
	A	B	C	D	E	F	G
1	Opportunity Owner	Opportunity Effective Date	Stage	Subscribers	Members	Funding	Active
2	Sales A	1/01/2020	Finalist	10000	20000	FI	Active
3	Sales B	1/01/2020	Closed Won	269	511	FI	Inactive
4	Sales B	1/01/2020	Submitted to Customer	107	214	FI	Inactive
5	Sales C	1/01/2020	Quoting	7020	13338	ASO	Active
6	Sales D	1/01/2020	Initiated	4625	9250	ASO	Active
7	Sales A	1/01/2020	Closed Lost	3294	6259	ASO	Inactive
8	Sales A	1/01/2020	Closed Won	320	600	ASO	Inactive
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=IF(ISNUMBER(MATCH([@Stage],{"Finalist","Submitted","Quoting","Initiated"},0)),"Active","Inactive")

@Yongle
I notice that you are using a very old version of XL2BB. There have been many enhancements and bug fixes since v1.0

 

[Yongle]

@Yongle
I notice that you are using a very old version of XL2BB. There have been many enhancements and bug fixes since v1.0

@Peter_SSs
Thank you