tweak my formula :)

[JacksonTyler]

=IF(ISNUMBER(MATCH(TRUE,COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!d4:d200"),E7)>0,0)),VLOOKUP(E7,INDIRECT("'"&INDEX($Z$1:$Z$10,MATCH(TRUE,COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!d4:d200"),E7)>0,0))&"'!d4:P200"),9,0),"")

how do i setup another if statement within this formula so that if the value is blank it doesn't show up as a "0"?

greatly appreciate the help
Thank you

 

[StephenCrump]

Will your VLOOKUP always return text values? If so, you could add a simple &"" to the end of your formula:

=IF(ISNUMBER(MATCH(TRUE,COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!D4:D200"),E7)>0,0)),VLOOKUP(E7,INDIRECT("'"&INDEX($Z$1:$Z$10,MATCH(TRUE,COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!D4:D200"),E7)>0,0))&"'!D4:P200"),9,0),"")&""

If VLOOKUP can return text or number values(?) it gets a little more complicated, because presumably you'll want to show zero if the VLOOKUP returns zero, but not if it returns blank?

Incidentally, if you have Excel 2007 or later, you could simplify your formula to:

=IFERROR(VLOOKUP(E7,INDIRECT("'"&INDEX($Z$1:$Z$10,MATCH(TRUE,COUNTIF(INDIRECT("'"&$Z$1:$Z$10&"'!D4:D200"),E7)>0,0))&"'!D4:P200"),9,0),"")