Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 30th 16, 08:04 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2016
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  
Old 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
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!A2100,4,0)))0 ),"N","Y")


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old May 31st 16, 06:22 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2016
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!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


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 09: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 12: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 11:28 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017