vlookup to stay blank if nothing selected

**stevesunfold** · 11-23-2008, 06:08 AM

hi guys
i have this formula which works well

Please Login or Register  to view this content.

which returns the value 0 if nothing selcted
how can i keep this blank if nothing is selected

thanks in advance

**stevesunfold** · 11-23-2008, 07:05 AM

dont know if im explaining well enough

but if nothing is selected in cell e29 then i want the cell with forula in to stay blank also

many thanks

**VBA Noob** · 11-23-2008, 07:12 AM

Your formula makes no sense.

The item to look up is within the lookup Range.

If it does return zero you could turn off zero values. Goto Tools > options > edit tab > untick zeros

or use an if statement

=IF(VLOOKUP($E29,$B:$E,2,0)=0,"",VLOOKUP($E29,$B:$E,2,0))

http://spreadsheetpage.com/index.php...s_in_formulas/

VBA Noob

**stevesunfold** · 11-23-2008, 07:40 AM

hi vba
the cell e29 is the look up reference

it then goes further down the sheet to look up whats in e29 and cross refernces it then returns the code

**VBA Noob** · 11-23-2008, 07:43 AM

the cell e29 is the look up reference

Yes I know and you're look at Cols B to E at Secound column.

So surely you're look up range would be Cols B to C?

VBA Noob

**stevesunfold** · 11-23-2008, 08:11 AM

HI VBA
yes yoy are right
i have ammended the code to

Please Login or Register  to view this content.

but it returns #na

or if i use your code

Please Login or Register  to view this content.

it returns #n/a

**daddylonglegs** · 11-23-2008, 08:21 AM

You can modify your formula like this

=IF($E29="","",VLOOKUP($E29,$B:$C,2,0))

You'll still get an #N/A error if E29 isn't found in column B

**martindwilson** · 11-23-2008, 08:40 AM

=if(isna(vlookup(e29,b:c,2,false)),"",if(vlookup(e29,b:c,2,false)=0,"",vlookup(e29,b:c,2,false)))

**stevesunfold** · 11-23-2008, 08:50 AM

perfect
cheers daddylonglegs

vlookup to stay blank if nothing selected

LinkBack

Thread Tools

Rate This Thread

Display

vlookup to stay blank if nothing selected

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions