IF Function Help

[Padthelad]

I'm having trouble inserting and extra if function at the beginning of my formula.

I have a 'Job sheet' which informs my workshop of what to produce based on what has been quoted (information for the job sheet is gathered from another sheet in the same workbook).

Currently, I have the following table:-
[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]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Building Type
[/TD]
[TD]Quantity
[/TD]
[TD]Size
[/TD]
[TD]Notes
[/TD]
[TD]Timber Skids
[/TD]
[TD]Galv Skids
[/TD]
[TD]Timber Base
[/TD]
[TD]Concrete Base
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Badminton
[/TD]
[TD]1
[/TD]
[TD]3x3
[/TD]
[TD]Window
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3x3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Windsor
[/TD]
[TD]1
[/TD]
[TD]4x4
[/TD]
[TD]no window
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Cell B5 contains the formula =IFNA(INDEX(Quote!$A$13:$A$31,MATCH(A5,Quote!$B$13:$B$31,0)),0) which returns the value in the column that relates to 'Badminton' on the quote sheet in the same workbook.

Cell C5 contains the formula =IFERROR(VLOOKUP(+A5,Quote!$B$13:$C$31,2,0),0) which returns the value in the 'size' column on the quote sheet.

Cell G5 has the formula =IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0) which returns the value in the corresponding column next to 'Timber base' from my quote sheet.

The problem I am having is that there are other items on my job sheet that could potentially have a 'timber base'. At the moment, when 'timber base' is entered on the quote sheet, all the 'timber base' job sheet items are highlighted.

One workaround I have considered inserting a set of IF function at the beginning of my current formula to effectively say 'if there is a value in B5 AND C5 then do the the formula ( =IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0) )'

OR:-

Is there a specific formula that can 'return timber base value that is related to building type'

I'm having trouble getting my head round the logic of this.

Any help anyone can provide is very much appreciated.

Thanks,

Pad

 

[Fishboy]

One workaround I have considered inserting a set of IF function at the beginning of my current formula to effectively say 'if there is a value in B5 AND C5 then do the the formula ( =IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0) )'

Hi Pad,

based on this suggestion, does this work?

=IF(AND(B5<>"",C5<>""),IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0),"")

 

[Padthelad]

Hi Pad,

based on this suggestion, does this work?

=IF(AND(B5<>"",C5<>""),IFERROR(VLOOKUP(+G4,Quote!$B$13:$C$31,2,0),0),"")

Hi Fishboy,

Thanks for your reply. The formula seems to work, but not quite as intended.

This may frustrate you, but I have decided to use a different formula in cell G5 which is:-

=IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0)

When I amend my formula to include yours:-

=IF(AND(B5<>"",C5<>""),IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0),"")

It still returns the value of having a 'timber base'. Which relates to another building, see below:-

[TABLE="width: 740"]
<colgroup><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Mobile Field Shelter[/TD]
[TD]Quantity[/TD]
[TD]Size[/TD]
[TD]Notes[/TD]
[TD]Timber Skids[/TD]
[TD]Galvanised Skids[/TD]
[TD]Timber Base[/TD]
[TD]Concrete Base[/TD]
[/TR]
[TR]
[TD]Badminton[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Windsor[/TD]
[TD="align: right"]1[/TD]
[TD]4x4[/TD]
[TD]left window[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Quote is showing as 'Windsor' with a 'timber base'. Formula in cell G6 is:-

=IF(AND(B6<>"",C6<>""),IFNA(INDEX(Quote!$A$13:$A$31,MATCH(G4,Quote!$B$13:$B$31,0)),0),"")

As you can see from the table above, the timber base is highlighted whether cells B6 or C6 have values in them.

Thanks for you help, further suggestions are gratefully received.

Thanks,

Pad

 

[Fishboy]

No worries Pad, but unfortunately for me the IFNA function does not work with the version of Excel I am rocking, so I am afraid there is little more use I can be here. I hope you are able to find a suitable solution mate