Index / Match with condition
 

Part 1
I have a spreadsheet with two tabs. In the first tab (Sites) I want to populate column B with a "Y" or "N" based on whether or not the site exists in the second tab (Status).

Part 2
I also want a condition in the formula to return an "N" for any site where the status (column B of second tab) is equal to "End of Life" or " Delete" regardless of whether or not the site matches.

Currently I have a formula in column B which accomplishes part 1 but haven't been able to successfully modify it to include part 2.

I have tried the following:

=IF(OR(Table1[Status]<"End of Life",Table1[Status]<"Delete"),IF(ISNA(MATCH(A2,Table1[ID],0)),"N","Y"))

However I still get a "Y" for those sites that have an "End of Life" or "Delete" status.

Any help is appreciated

Regards,

Index / Match with condition
 

Hi,

Am Mon, 30 May 2016 20:04:12 +0100 schrieb b_nuruddin:

Part 1
I have a spreadsheet with two tabs. In the first tab (Sites) I want to
populate column B with a "Y" or "N" based on whether or not the site
exists in the second tab (Status).

Part 2
I also want a condition in the formula to return an "N" for any site
where the status (column B of second tab) is equal to "End of Life" or "
Delete" regardless of whether or not the site matches.

modify the range and the column in the formula to your table:
=IF(OR(COUNTIF(Status!A:A,A2)=0,COUNT(FIND({"End of Life";"Delete"},VLOOKUP(A2,Status!A2:D100,4,0)))0 ),"N","Y")


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Thanks once again for the speedy solution to my excel dilemma.

Any recommendations for training resources that will allow me to understand / write formulas like this. I have a basic understanding of the more common formulas but have trouble putting together the more complex ones.

Regards
Nuruddin