#N/A error when lookup value is blank and lookup vector has a blank with VLOOKUP

[Excel_VBA]

I am using a VLOOKUP on a 2 column table array. Each value in column 1 has a corresponding value in column 2. However there is a case where in column1 I have a blank cell with the corresponding value for a cell that is blank cell.

When my VLOOKUP formula encounters a blank cell for the Lookup value it returns a #N/A.

In short, I want the VLOOKUP to say, when the lookup value is a blank cell, go to the table array, find the blank cell in column one and return the corresonding value in column 2 of the table array.

Is it possible to use a blank cell as a lookup value? Is this why I have the #N/A error?

 

[Excel_VBA]

Let's assume one of those blank cells is J10. What result do you get from these formulas:

=LEN(J10)

=CODE(J10)

The LEN function returns 0. The CODE function returns #VALUE.

 

[T. Valko]

The LEN function returns 0. The CODE function returns #VALUE.

Ok, well, it's still undetermined what might be in those cells that appear blank but this tweak to my other suggested formula should work:

=IF(LEN(A1),VLOOKUP(A1,E$1:F$6,2,0),F$6)