Remember Me?

#1
May 30th 16, 08:04 PM
 Junior Member First recorded activity by ExcelBanter: May 2016 Posts: 4
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,

#2
May 30th 16, 10:51 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,853
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!A2100,4,0)))0 ),"N","Y")

Regards
Claus B.
--
Windows10
Office 2016
#3
May 31st 16, 06:22 PM
 Junior Member First recorded activity by ExcelBanter: May 2016 Posts: 4

Quote:
 Originally Posted by Claus Busch Hi, 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!A2100,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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post io Excel Worksheet Functions 0 February 23rd 13 09:08 AM JMeier Excel Worksheet Functions 8 August 1st 08 01:45 AM need a help[_2_] Excel Discussion (Misc queries) 6 July 23rd 08 11:22 PM Julie Olsen Excel Worksheet Functions 3 December 29th 06 12:50 AM Brisbane Rob Excel Worksheet Functions 3 September 24th 05 10:04 PM

All times are GMT +1. The time now is 11:28 AM.