Formula returning blank cell

[kimberleycaldwell]

Hello,

I have inputted a formula that looks up data from another tab, however, it is returning nothing (ie blank cell). It works perfectly in another work book however we cannot get it to work in the file it needs to be in. We know the formula works, we have checked formatting, conditional formatting, colour of text etc.

Can anyone help??

Thanks,
Kim

 

[prabby25101981]

Can you post the formula?

 

[Andrew Poulsom]

What's the formula? Maybe you have one of the problems described here:

http://www.contextures.com/xlFunctions02.html#Trouble

 

[p45cal]

What's the actual formula?
Usually if the cell returned is blank, vlookup returns a zero (unless you've turned on displaying zeroes as blank).

 

[kimberleycaldwell]

Hello,

the formula is below:

=IF(ISERROR(VLOOKUP($A17&"-"&COLUMN()-1,database!$A$3:$C$2070,3,0)),"",IF(VLOOKUP($A17&"-"&COLUMN()-1,database!$A$3:$C$2070,3,0)=0,"",VLOOKUP($A17&"-"&COLUMN()-1,database!$A$3:$C$2070,3,0)))

database refers to another tab in the document.

We have tested this formula in another spreadsheet and seems to work???

Thanks.

 

[p45cal]

There are two possible reasons that it returns a blank cell; either
VLOOKUP($A17&"-"&COLUMN()-1,database!$A$3:$C$2070,3,0)
returns an error or it returns 0.

Presumably the formulae above/below this are the same, so you'll be able to copy the cell formula from the cell below, so temporarily simplify the formula in the cell returning blank with just:
=VLOOKUP($A17&"-"&COLUMN()-1,database!$A$3:$C$2070,3,0)
What does it return now?
Depending on the result, you'll alter the formula or…

BTW, one thing to note, the COLUMN() bit returns values which are dependent on which column the formula itself is in.. so you may not be looking up what you expect. To test this highlight:
$A17&"-"&COLUMN()-1
in the formula bar and press F9. Do you see the result you expect?

 

[kimberleycaldwell]

hey,
will test this tomorrow with my collegue....
Thanks, let you know in the morning. x