IF (or similar) based on various list of options

RobMolyneux

New Member
Joined
Sep 14, 2016
Messages
18
Hi,

I'm sorry for the title being so vague, but i quickly need to run some numbers and the source data is a bit messy. The simplest way for me to do this with my limited knowledge is an IF statement, but i think it's going to be very messy in terms of nested IFs to do what i need, so i wonder if anyone can help.

I have columns A to AM which come from a macro of some sort and so change content when refreshed, but then there are cells AN to AQ which self populate according to the data in A-AM, and so update automatically as and when the sheet is refreshed.

I hope that makes sense.

I want to add in another column which will return one of "Live", "On Hold" or "Waiting", depending on what is in the main data in columns A-AM.

I'm working in a worksheet called "PMData" if that matters.

in column K (titled "jobType") i have a series of work types, call then "AAA", "BBB", "CCC", etc. of which i only need to care about "AAA"
in Column H (titled "ProjectManagerStatus") i have 11 different statuses in total, of which some are essentially the same as another for my purposes in what i'm trying to do, and 3 others i want to ignore completely.

I've created a new worksheet called "LIST" which lists these 11 statuses out in the range A1 to A11.

I want to return my own statuses "Live", "On Hold" or "Waiting" in column AR based on:

IF ----> K = "AAA"

AND ---> IF ---> H = any of A1-A6 in LIST sheet ----> then "Live"

. OR IF ----> H = A7 in LIST sheet ----> then "On Hold"

. OR IF ----> H = A8 in LIST sheet ----> then "Waiting"

so if K = anything other than "AAA" or if K = "AAA" but H = anything other than A1-A8 in LIST sheet, then it doesn't return anything in column AR.


I'm not sure if that will be enough for anyone to work with, or if i've even explained it right, but I've found this forum to dig me out of a hole when i've been utterly stuck before, so my fingers are firmly crossed once again, and thanks in advance!!!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">LIST1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">LIST2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">LIST3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">LIST4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">LIST5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">LIST6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">LIST7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">LIST8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">LIST9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">LIST10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">LIST11</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">LIST</p><br /><br />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>AQ</th><th>AR</th><th>AS</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">ProjectManagerStatus</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">jobType</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Live</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Live</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Live</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Waiting</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">On Hold</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">AAA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">PMData</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AR2</th><td style="text-align:left">=IF(<font color="Blue">H2=$AR$1,IF(<font color="Red">MATCH(<font color="Green">K2,LIST!$A$1:$A$11,0</font>)<=6,"Live", IF(<font color="Green">MATCH(<font color="Purple">K2,LIST!$A$1:$A$11,0</font>)=7,"On Hold", IF(<font color="Purple">MATCH(<font color="Teal">K2,LIST!$A$1:$A$11,0</font>)=8,"Waiting",""</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,215
Members
416,847
Latest member
inaramos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top