# tweak my formula :)

#### JacksonTyler

##### New Member
=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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### StephenCrump

##### Well-known Member
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),"")

Replies
5
Views
100
Replies
8
Views
115
Replies
13
Views
250
Replies
6
Views
87
Replies
17
Views
237