Differentiating between 0 and empty cell with VLOOKUP in Google Sheets

Question

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.

Link to example sheet

Answer 1

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), 
  lookup, 'Grade Boundaries'!$A$3:$B$10, 
  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().