Add If to beginning of indirect lookup formula

[rhombus4]

From this threadhttp://www.mrexcel.com/forum/showthread.php?t=544974Currently have =VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)What I need is to add an If at the beginning so for example if cell C5 was blank or 0 then the result would be " or 0.i.e. something like=if(""&A1&"!c5="","",(VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

 

[rhombus4]

Difficult to explain.I have=VLOOKUP($B$2,INDIRECT("'"&$A1&"'!B2:B5"),2,0)) If I manually enter an if below it works=IF(Sheet2!M4="","",VLOOKUP($B$2,INDIRECT("'"&$A1&"'!B2:B5"),2,0))As I have lots to copy down I want where it says Sheet2 to refer to a tab which is in column A like the vlookup looks for VLOOKUP($B$2,INDIRECT("'"&$A1&"'!B2:B5"),2,0))

 

[jasonb75]

Difficult to explain.

I have =VLOOKUP($B$2,INDIRECT("'"&$A1&"'!B2:B5"),2,0))

If I manually enter an if below it works =IF(Sheet2!M4="","",VLOOKUP($B$2,INDIRECT("'"&$A1&"'!B2:B5"),2,0))

As I have lots to copy down I want where it says Sheet2 to refer to a tab which is in column A like the vlookup looks for

VLOOKUP($B$2,INDIRECT("'"&$A1&"'!B2:B5"),2,0))

Your posts are difficult to read, breaking it up a little makes it far easier to see.

Where did M4 come into things?

You asked to check C5, C6, etc so the formula I gave you was doing just that, it's not looking anywhere near M4.

 

[rhombus4]

=if(indirect("'"&A1&"'!C5")="","",VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

does it work in the first row?

if you copy it down, does it work in the second row?

It works in the first row and all other rows if a number is in cell C5, although when C5 is blank it shows a 0

 

[rhombus4]

in your formula =if(indirect("'"&A1&"'!C5")="","",VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0)) when I copy down =if(indirect("'"&A1&"'!C5")="","", the A1 changes to a2 a3 etc as it should but the C5 stays as c5 when copied down

 

[jasonb75]

Try it this way instead

=if(indirect(ADDRESS(ROW(A5),3,4,,A1))="","",VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

 

[rhombus4]

The formula works on the first cell when blanked but when copied down shows 0

 

[rhombus4]

Try it this way instead

=if(indirect(ADDRESS(ROW(A5),3,4,,A1))="","",VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

in your formula what does the 3,4,, do?

 

[jasonb75]

3 is column number (column C)
4 tells the formula to return a relative address
the extra comma skips over an optional parameter that we don't need.

If for example you have "Sheet 2" in A1, then ADDRESS(5,3,4,,A1) will return 'Sheet2'!C5

Using ROW(A5) to define the row number means it becomes relative, so when you fill down A5 changes to A6, so will be 'Sheet x'!C6, etc.

 

[rhombus4]

Try it this way instead

=if(indirect(ADDRESS(ROW(A5),3,4,,A1))="","",VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0))

comes up with Ref# errorthis is what I have in sheet 1Tom is B1101 starts A4so the formula in the stats which equals 12 is =IF(INDIRECT("'"&$A4&"'!C2")="","",VLOOKUP($B$1,INDIRECT("'"&$A4&"'!B2:D5"),2,0)) Tom Stats Errors101 12 101801 15 1001501 15 1112201 16 1012901 19 85

 

[rhombus4]

OK I now get the formula to work=IF(INDIRECT("'"&$A4&"'!C2")="","",VLOOKUP($B$1,INDIRECT("'"&$A4&"'!B2:D5"),2,0))However when you first askedIs it just C5 you want to look at for blank / 0 or the result of the VLOOKUP? I actually have other sheets which I wanted to copy down, but the results relate to the first one.i.e.c2 is okay on sheet 1 but on sheet2 needs to be c3