1

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

1

1 Answer 1

3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.