Stop Vlookup bringing back 0's or #REF! and bring back blanks instead

[Carling73]

Evening

After a full day of trying I've got down to two different formula but i'm having a problem with each one, Option 1 brings back 0's when there is no text in the cell Vlookup is searching, whilst Option 2 solves the 0's but then brings back #REF! when D2 on the "Control Sheet" is blank.

Just for information the Staff members name that I use in the D column (D2, D3 etc) is only added once a client is allocated, before which it is left blank

Any ideas how to get Option 1 below to stop showing 0's or how to get Option 2 below to stop showing #REF!

Option 1

=IF('Control Sheet'!D2<>"",VLOOKUP(A2,INDIRECT('Control Sheet'!D2&"!$A:$K"),11,0),"")


Options 2

=T(VLOOKUP(A2,INDIRECT("'"&'Control Sheet'!D2&"'!A:K"),11,0))

[Carling73]

Sorted it


=IF('Control Sheet'!D2="","",T(VLOOKUP(A2,INDIRECT("'"&'Control Sheet'!D2&"'!A:K"),11,0)))

[newdoverman]

The definition of the Table array in the VLOOKUP is what is causing the errors. When D2 is blank, the formula jumps to the False part of the If statement which is "". When the statement is TRUE it processes the VLOOKUP which cannot use the array definition that you have given.

If you could post a workbook with generic data there would be a better chance of finding out exactly what you are trying to do and then provide an answer to you.