Wednesday, May 12, 2010

Use empty string instead of zeros for failed VLOOKUP

When using VLOOKUP in Excel/NodeXL, you may often get zeros when looking for text and it isn't found. In many cases you may want an empty string instead. For example
=VLOOKUP([@[Vertex 2]],Vertices,COLUMN(Vertices[Flags]),FALSE)
returns 0 when the Flags column for Vertex 2 in the Vertices worksheet is empty. Our tools work better with empty cells. So rather than write something gross like
=IF(VLOOKUP([@[Vertex 2]],Vertices,COLUMN(Vertices[Flags]),FALSE)=0,"",VLOOKUP([@[Vertex 2]],Vertices,COLUMN(Vertices[Flags]),FALSE))
that does the search twice, just use the T function that automatically turns it into text.
=T(VLOOKUP([@[Vertex 2]],Vertices,COLUMN(Vertices[Flags]),FALSE))