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))

Better vlookup for edges in NodeXL

I recently was using NodeXL and had to use VLOOKUP to copy vertex info over to the edge worksheet. Rather than having to deal with obtuse indexes and the like you can refer to the columns and worksheets by name. For example, instead of something like this as your formula
=VLOOKUP(A3,Vertices!1:1048576,29,FALSE)
you can use this instead
=VLOOKUP([@[Vertex 1]],Vertices,COLUMN(Vertices[Pub Year]),FALSE)
This uses NodeXL's named worksheets and columns instead. Also, counting offsets can get pretty tedious, especially with how NodeXL hides columns and how many of them there are. By using the COLUMN function you don't have to!