VLookup...returning zeros when cells are blank, how to have them appear blank.

[treym]

I have a file I'm using at work to identify skill levels. The file has been designed using a drop down box (that selects a skill set). A Vlookup then returns the text information in the appropriate cells. If the cells have nothing in them, then the vlookup returns a '0'. How can I adjust the code so it returns just a blank cell instead?

 

[Aladin Akyurek]

I have a file I'm using at work to identify skill levels. The file has been designed using a drop down box (that selects a skill set). A Vlookup then returns the text information in the appropriate cells. If the cells have nothing in them, then the vlookup returns a '0'. How can I adjust the code so it returns just a blank cell instead?

Try something like...

=T(VLOOKUP(...))

 

[T. Valko]

I have a file I'm using at work to identify skill levels. The file has been designed using a drop down box (that selects a skill set). A Vlookup then returns the text information in the appropriate cells. If the cells have nothing in them, then the vlookup returns a '0'. How can I adjust the code so it returns just a blank cell instead?

What is the data type of the values being returned by the lookup?

Are they text? Numbers? Could be either?

 

[treym]

It is all strictly text. There are say 20 different 'tables', with a skill level running from left to right across the top, then several categories down the left. The squares in between is the matrix. When you choose the dropdown, you choose one of the skill sets and just that page shows (not all 20). Some tables are greater than others and hence you get some '0's.

 

[T. Valko]

It is all strictly text. There are say 20 different 'tables', with a skill level running from left to right across the top, then several categories down the left. The squares in between is the matrix. When you choose the dropdown, you choose one of the skill sets and just that page shows (not all 20). Some tables are greater than others and hence you get some '0's.

Ok, try this...

Book1
	A	B
2	Data1	North
3	Data2	East
4	Data3	_
5	Data4	South
6	Data5	West
7	Data6	West
8	Data7	West
9	Data8	East
10	Data9	_
Sheet3

Let's assume you lookup "Data3"...

=T(VLOOKUP("Data3",A2:B10,2,0))

That will return a blank.

 

[Aladin Akyurek]

It is all strictly text. There are say 20 different 'tables', with a skill level running from left to right across the top, then several categories down the left. The squares in between is the matrix. When you choose the dropdown, you choose one of the skill sets and just that page shows (not all 20). Some tables are greater than others and hence you get some '0's.

See my reply...

 

[treym]

Adding the T to the front seemed to work. Thanks! Could you please explain how that worked?

 

[T. Valko]

Adding the T to the front seemed to work. Thanks! Could you please explain how that worked?

The T( ) function stands for TEXT.

If the value being evaluated by the function is text, it returns that value. Otherwise, it returns the blank string "" (except if the value being evaluated is a logical error, for example, #N/A).

Book1
	A	B
1	text	text
2	10
3	#N/A	#N/A
4	this	this
5	_
Sheet1

Formula entered in B1 and copied down to B5:

=T(A1)

 

[T. Valko]

The T( ) function stands for TEXT.

If the value being evaluated by the function is text, it returns that value. Otherwise, it returns the blank string "" (except if the value being evaluated is a logical error, for example, #N/A).

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">text</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">#N/A</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">#N/A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">this</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">this</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid"> </TD></TR></TBODY></TABLE>


Formula entered in B1 and copied down to B5:

=T(A1)

So, if your VLOOKUP corresponds to an empty cell which will evaluate to 0 then:

=T(0) will return the blank "".

 

[Aladin Akyurek]

Adding the T to the front seemed to work. Thanks! Could you please explain how that worked?

T(Value)

return Value as is if it's text, otherwise "" (a blank) if it's not an error value.

The function is the opposite of N(Value) in behavior, except when Value is a truth value (TRUE or FALSE).