Adding an extra Function

[Padthelad]

Hi,

I have a problem where I need to add an extra function to a formula. I currently have the following table on a worksheet:-

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]Log Cabin
[/TD]
[TD]Quantity
[/TD]
[TD]Size
[/TD]
[TD]Notes
[/TD]
[TD]Twin Skin
[/TD]
[TD]Insulate Floor
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]Cowbeech
[/TD]
[TD]1
[/TD]
[TD]3x3
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]Brightling
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula in cell E19 is:-

=IF($B19>=1,IFNA(INDEX(Quote!$A$13:$A$31,MATCH($E$18,Quote!$B$13:$B$31,0)),0),$C19>=1).

This is basically saying that 'if there is a value in cell B19 AND C19 then carry out the INDEX and MATCH using another worksheet.

I would like help with inserting another function to say 'if there is anything in cell B19, C19 AND D19, then carry out the INDEX and MATCH functions'. Ideally I would like to say 'if there is a certain phase in D19 "Delivery Only" then carry out the INDEX and MATCH functions'.

For some reason I'm having trouble getting my head round the logic of this. I keep getting an error message saying that there are too many arguments in the formula.

Any help anyone can provide is very much appreciated.

Many thanks,

Pad

 

[ghrain22]

Seems like you're asking 2 different questions.

I would like help with inserting another function to say 'if there is anything in cell B19, C19 AND D19, then carry out the INDEX and MATCH functions'.
Pad

With ctrl+shft+enter:

[COLOR=#333333]=IF(sum(len(B19:D19))>0,IFNA(INDEX(Quote!$A$13:$A$31,MATCH($E$18,Quote!$B$13:$B$31,0)),0),$C19>=1)[/COLOR]

Ideally I would like to say 'if there is a certain phase in D19 "Delivery Only" then carry out the INDEX and MATCH functions'.

[COLOR=#333333]=IF(D19="Delivery Only",IFNA(INDEX(Quote!$A$13:$A$31,MATCH($E$18,Quote!$B$13:$B$31,0)),0),$C19>=1)[/COLOR]

 

[bhos123]

=IF(And($B19>=1,$C19>=1,$D19="abc"),IFNA(INDEX(Quote!$A$13:$A$31,MATCH($E$18,Quote!$B$13:$B$31,0)),0),$C19>=1).

 

[Fishboy]

Hi,

I have a problem where I need to add an extra function to a formula. I currently have the following table on a worksheet:-

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Log Cabin
[/TD]
[TD]Quantity
[/TD]
[TD]Size
[/TD]
[TD]Notes
[/TD]
[TD]Twin Skin
[/TD]
[TD]Insulate Floor
[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Cowbeech[/TD]
[TD]1[/TD]
[TD]3x3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Brightling[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula in cell E19 is:-

=IF($B19>=1,IFNA(INDEX(Quote!$A$13:$A$31,MATCH($E$18,Quote!$B$13:$B$31,0)),0),$C19>=1).

This is basically saying that 'if there is a value in cell B19 AND C19 then carry out the INDEX and MATCH using another worksheet.

I would like help with inserting another function to say 'if there is anything in cell B19, C19 AND D19, then carry out the INDEX and MATCH functions'. Ideally I would like to say 'if there is a certain phase in D19 "Delivery Only" then carry out the INDEX and MATCH functions'.

For some reason I'm having trouble getting my head round the logic of this. I keep getting an error message saying that there are too many arguments in the formula.

Any help anyone can provide is very much appreciated.

Many thanks,

Pad

Hi Pad,

If I am understanding you correctly then it seems you ONLY want to do the Index / Match function if ALL 3 criteria are met, so B19 must have a value, C19 must have a value AND D19 must have a value of "Delivery Only". If that is correct you could try this:

=IF(AND(B20>0,C20>0,D20="Delivery Only"),IFNA(INDEX(Quote!$A$13:$A$31,MATCH($E$18,Quote!$B$13:$B$31,0)),0),"")