Need to Return zero

[kygwalt]

I need help, the formula below works with the exception that I would like it to return a 0 if False right now it is returning #NA. Can anyone see what I'm doing wrong?? Thanks for any help.

=IF(COUNTIF(STYLE!$A$1:$A$2,"x"),VLOOKUP(D12,INDIRECT(INDEX(STYLE!$C$1:$C$2,MATCH("X",STYLE!$A$1:$A$2,0))),INDEX(STYLE!$D$1:$D$2,MATCH("X",STYLE!$A$1:$A$2,0)),FALSE),0)

[Bernie Deitrick]

INDIRECTs are hard to troubleshoot without the workbook and if what is false?

[Craig K.]

The first argument in your IF() function isn't a "logical test" where it needs to compare two values. The way it's set up, it's always going to return a value of TRUE, and therefore whatever value is in your VLOOKUP(). When a value cannot be found in a vlookup, it will return a "#N/A".

[Bernie Deitrick]

The conditional part of the if (the COUNTIF) will return 0 or a number greater than 0. 0 is False, and anything >0 is True, so that part will work.

[anand_babu123]

Can u upload sample data ?

[kygwalt]

I hope this attachment works. The formula is on sheet 3 column "E" Thanks

ARCHITECTURAL PRICE AND WEIGHTS.xls

[Ace_XL]

Do you need to refer to two Rows on the STYLE Sheet? I see no connection between the two rows with the "X"

Maybe you want..

=IF(D12="",0,IF(COUNTIF(STYLE!$A$1:$A$2,"x"),VLOOKUP(D12,INDIRECT(INDEX(STYLE!$C$1:$C$2,MATCH("X",STYLE!$A$1:$A$2,0))),INDEX(STYLE!$D$1:$D$2,MATCH("X",STYLE!$A$1:$A$2,0)),FALSE),0))

else..

=IF(COUNTIF(STYLE!$A$1:$A$1,"x"),VLOOKUP(D12,INDIRECT(INDEX(STYLE!$C$1:$C$2,MATCH("X",STYLE!$A$1:$A$2,0))),INDEX(STYLE!$D$1:$D$2,MATCH("X",STYLE!$A$1:$A$2,0)),FALSE),0)