Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Junior Member
 
Posts: 4
Thumbs up

Quote:
Originally Posted by Claus Busch View Post
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!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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX MATCH Functions in LibreCalc (Excel) - Non unique valuesreturn wrong index result io Excel Worksheet Functions 0 February 23rd 13 10:08 AM
index(match) Wind Uplift Calculations (match four conditions) JMeier Excel Worksheet Functions 8 August 1st 08 01:45 AM
index-match and another condition need a help[_2_] Excel Discussion (Misc queries) 6 July 23rd 08 11:22 PM
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 01:50 AM
index,match,match on un-sorted data Brisbane Rob Excel Worksheet Functions 3 September 24th 05 10:04 PM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"