Formula returning blank cell

kimberleycaldwell

New Member
Joined
Jul 22, 2010
Messages
7
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. :mad:

Can anyone help??

Thanks,
Kim
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

New Member
Joined
Jul 22, 2010
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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?
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,428
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top