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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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).
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,218,690
Messages
6,143,940
Members
450,516
Latest member
shironokuro

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
Back
Top