tweak my formula :)

JacksonTyler

New Member
Joined
Oct 5, 2014
Messages
5
=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
 

Some videos you may like

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
Joined
Sep 18, 2013
Messages
3,804
Office Version
  1. 365
Platform
  1. Windows
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),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,030
Messages
5,526,344
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top