When doing data manipulation in Excel I frequently write vLookup statements, but “no match” ends up showing up as #N/A if you pass the FALSE parameter to the [range_lookup] option.

To avoid that, use a formula like this:

=IF(ISNA(VLOOKUP(B2,$AX$2:$AY$14,2,FALSE)),"",VLOOKUP(B2,$AX$2:$AY$14,2,FALSE))

The ISNA() function is the key to detecting the #N/A values and then just substituting them with a zero length string (the two double-quotes).

Advertisements