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:-

	A	B	C	D	E	F
18	Log Cabin	Quantity	Size	Notes	Twin Skin	Insulate Floor
19	Cowbeech	1	3x3		1	1
20	Brightling
21

<tbody>
</tbody>

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:-

	A	B	C	D	E	F
18	Log Cabin	Quantity	Size	Notes	Twin Skin	Insulate Floor
19	Cowbeech	1	3x3		1	1
20	Brightling
21

<tbody>
</tbody>

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),"")