Excel Add Text Based on Cell Value

To display text based on another cell, you have to use the conditional function IF.

=IF(A2<51,"Bottom 50%","Top 50%")

The function displays the value in the first argument (“Bottom 50%”) if the condition is met, otherwise it displays the text “Top 50%”.

It becomes more complicated when there more conditions. Let’s say that we need more conditions:

• Bottom 25%
• Bottom 50%
• Top 50%
• Top 25%

Now our function will look like this.

=IF(A2<26,"Bottom 25%",IF(A2<51,"Bottom 50%",IF(A2>75,"Top 25%","Top 50%")))

As you can see, it becomes really complicated even for such a low number of conditions.

Instead of using this way to create a formula for more conditions, you can create a function in VBA that will display the desired text.

Add text based on a cell value in VBA

To do this we are going to use a function with the Select … Case statement. The function will take one parameter, which is a reference to a cell.

Create a new module and enter the following formula.

Function CheckValue(cell As Range)
    Select Case cell
        Case 1
            CheckValue = "Bottom 1%"
        Case 2 To 10
            CheckValue = "Bottom 10%"
        Case 11 To 25
            CheckValue = "Bottom 25%"
        Case 26 To 50
            CheckValue = "Bottom 50%"
        Case 51 To 75
            CheckValue = "Top 50%"
        Case 76 To 90
            CheckValue = "Top 25%"
        Case 90 To 99
            CheckValue = "Top 10%"
        Case 99 To 100
            CheckValue = "Top 1%"
        Case Else:
            CheckValue = "Not in range"
    End Select
End Function

Type =CheckValue(A2) into cell B2 and autofill for the rest of the cells in the column.

This is a great way to write such functions because it’s more readable and can be easily modified.