Multiple IF, AND & OR Formula - Help Needed.

[Retsu]

I'm struggling to write a working formula that contains all the arguments below to return the correct result.

IF D3 = "4" OR "8" AND G3 = "1" then "8DM11"
IF D3 = "4" OR "8" AND G3 = "2" then "8DM21"
IF D3 = "A" AND G3 = "1" then "8DM11"
IF D3 = "A" AND G3 = "2" then "10NM1"

Any help on this would be appreciated.

Taylor

 

[Aladin Akyurek]

I'm struggling to write a working formula that contains all the arguments below to return the correct result.

IF D3 = "4" OR "8" AND G3 = "1" then "8DM11"
IF D3 = "4" OR "8" AND G3 = "2" then "8DM21"
IF D3 = "A" AND G3 = "1" then "8DM11"
IF D3 = "A" AND G3 = "2" then "10NM1"

Any help on this would be appreciated.

Taylor

Try...

=IF(OR(G3={1,2}),IF(OR(D3={4,8}),"8DM11",IF(D3="A",
    INDEX({"8DM11","10NM1"},G3),"")),"")

 

[Retsu]

Aladin,

The formula you suggested seems to exclude a number of the combinations.
Below I've provided a more specific list of matches that should display results.
Anything other than those matches needs to return a nil value.

IF D3 = 4 & G3 = 1 then L3 = "8DM11"
IF D3 = 8 & G3 = 1 then L3 = "8DM11"

IF D3 = 4 & G3 = 1 then L3 = "8DM21"
IF D3 = 8 & G3 = 1 then L3 = "8DM21"

IF D3 = "T","V & G3 = 1 then L3 = "8DM11"
IF D3 = "T","V & G3 = 1 then L3 = "10NM1"

You may notice I've replaced the value of "A" for two other variables "T" and "V", I changed this to provide more flexibility within other areas of the workbook.

 

[Aladin Akyurek]

Aladin,

The formula you suggested seems to exclude a number of the combinations.

I think it pretty much covers the set you mentioned in your initial post...

Below I've provided a more specific list of matches that should display results.
Anything other than those matches needs to return a nil value.

IF D3 = 4 & G3 = 1 then L3 = "8DM11"
IF D3 = 8 & G3 = 1 then L3 = "8DM11"
 
IF D3 = 4 & G3 = 1 then L3 = "8DM21"
IF D3 = 8 & G3 = 1 then L3 = "8DM21"
 
IF D3 = "T","V & G3 = 1 then L3 = "8DM11"
IF D3 = "T","V & G3 = 1 then L3 = "10NM1"

You may notice I've replaced the value of "A" for two other variables "T" and "V", I changed this to provide more flexibility within other areas of the workbook.

What happened to G3 = 2? Also, the last two rules have the same conditions.

 

[Retsu]

My bad, the second set of conditions should be G3 = 2 as you rightly pointed out.

When I tried your formula I wasn't able to get the result of 8DM21.

 

[Aladin Akyurek]

My bad, the second set of conditions should be G3 = 2 as you rightly pointed out.

When I tried your formula I wasn't able to get the result of 8DM21.

Create a new sheet, called Admin.

Set up the following in Admin:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Cond-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Cond-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Result</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Concat</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8DM11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>4|1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8DM11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8|1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8DM21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>4|2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8DM21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8|2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>T</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8DM11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>T|1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>V</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>8DM11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>V|1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>T</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>10NM1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>V|2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>V</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>10NM1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>T|2</TD></TR></TBODY></TABLE>

A2:C9 houses the conditions you listed.

In D2 enter and copy down:

=A2&"|"&B2

Select D2:D9, name the selection CONDITIONS (via the Name Box) and
C2:C9 RESULTS.

Now you can invoke:

=IF(ISNUMBER(MATCH(D3&"|"&G3,CONDITIONS)),INDEX(RESULTS,MATCH(D3&"|"&G3,CONDITIONS)),"")

By the way, if needed, adjust the table area on Admin to suit.

 

[Retsu]

I created the index table on a new sheet as you suggested and majority of the conditions display the correct results, the only exception is

Cond-1	Cond-2	Result	Concat
4	2	8DM21	4|2

It gives the result of 8DM11, any ideas why this could be?

 

[Aladin Akyurek]

I created the index table on a new sheet as you suggested and majority of the conditions display the correct results, the only exception is

Cond-1    Cond-2    Result    Concat
4    2    8DM21    4|2

It gives the result of 8DM11, any ideas why this could be?

I forgat adding the match type to MATCH...

=IF(ISNUMBER(MATCH(D3&"|"&G3,CONDITIONS,0)),INDEX(RESULTS,MATCH(D3&"|"&G3,CONDITIONS,0)),"")

 

[Retsu]

Works like a charm!

One last thing if its not too much trouble :]
I have a formula that's working just fine, however I wan't to make it so that a zero value isn't displayed.

=" x"&IF(F4>0,AND(OR(D4={"V","T"}))*F4,"")

That's what I have so far, it's purpose is to show multiplies of a specific code.
The issue I'm having is when cell D4 = 4 or 8 it results in the cell being displayed as " x0 " when there are no multiples and I was hoping for it to come up as just " x ".

I've gone through the advanced options and removed the check mark to display zero values but It doesn't apply to this formula due to the text prefix I imagine.

 

[Aladin Akyurek]

Works like a charm!

One last thing if its not too much trouble :]
I have a formula that's working just fine, however I wan't to make it so that a zero value isn't displayed.

=" x"&IF(F4>0,AND(OR(D4={"V","T"}))*F4,"")

That's what I have so far, it's purpose is to show multiplies of a specific code.
The issue I'm having is when cell D4 = 4 or 8 it results in the cell being displayed as " x0 " when there are no multiples and I was hoping for it to come up as just " x ".

I've gone through the advanced options and removed the check mark to display zero values but It doesn't apply to this formula due to the text prefix I imagine.

Is this what you are after?...

=" x"&IF(OR(D4={"V","T"}),IF(N(F4),F4,""),"")