Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Quote:
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX MATCH Functions in LibreCalc (Excel) - Non unique valuesreturn wrong index result | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index-match and another condition | Excel Discussion (Misc queries) | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |