# Differentiating between 0 and empty cell with VLOOKUP in Google Sheets

I am trying to use VLOOKUP in this Google Sheet to convert percentage grades to a 1–8 grading scale. I would like to be able to differentiate between a score of 0 and an empty score but am having trouble making this work.

Here's the current formula I'm using:
`=IF(ISBLANK(VLOOKUP((C2/\$C\$6),'Grade Boundaries'!\$A\$3:\$B\$10,2,true)),"",VLOOKUP((C2/\$C\$6),'Grade Boundaries'!\$A\$3:\$B\$10,2,true))`

Current behavior

The Adjusted column shows 1 whether the corresponding Raw value is 0 or blank.

Desired behavior

The Adjusted column should show 1 if the corresponding Raw value is 0, or N/A if the corresponding Raw value is blank.

I've tried looking at other suggestions here and using IF statements, but I seem to keep getting stuck with the fact that VLOOKUP doesn't appear to differentiate between a value of zero and an empty cell.

The question is related to a larger issue, namely the equality of various "blank" or "zero" values when accessed by spreadsheet functions.

Most spreadsheet functions will consider truly blank values equal to zero-length text strings `""`. Conversely, many of them will consider truly blank values equal to zero, but zero-length text strings `""` not equal to zero. To add to the mix, some spreadsheet functions will not consider `""` blank, notably `isblank()` and `counta()`:

value how to get it `=value=0` `=value=""` `=isblank(value)` `=counta(value)`
truly blank value `=iferror(1/0)` TRUE TRUE TRUE 0
zero-length string `=""` FALSE TRUE FALSE 1
zero `0` TRUE FALSE FALSE 1

One way to differentiate between truly blank values, zero-length text strings and zeros is to use a combination of `isblank()` and `to_text()` to get the value in the cell as a text string in the format it shows in the spreadsheet, like this:

``````=ifs(
isblank(C2), na(),
to_text(C2) = "0", 1,
C2 = "", iferror(1/0),
true, vlookup(C2 / \$C\$6, 'Grade Boundaries'!\$A\$3:\$B\$10, 2, true)
)
``````

To make the formula easier to read, assign names with `let()`:

``````=let(
cell, C2,
percent, to_percent(cell / \$C\$6),
ifs(
isblank(cell), na(),
to_text(cell) = "0", 1,
cell = "", iferror(1/0),
true, vlookup(percent, lookup, columns(lookup), true)
)
)
``````

Note that `to_text()` gets the value in the cell as a text string in the format it shows in the spreadsheet. For example, if the values are formatted as Format > Number > Number, you need compare against `"0.00"`.

See to_text() and let().

• Very helpful explanation and solution. Thank you! Mar 14 at 4:08