How to index match a condition set in a cell

Question

I am trying to avoid having a multiple if formula by index matching a table instead, however what i need to match is the actual condition and a string.

Lookup table:

+---+------------------+-------------------+-------+
|   |        A         |         B         |   C   |
+---+------------------+-------------------+-------+
| 1 | Current to Prior | Portfolio Comment | Error |
| 2 | =0               | ""                | 1     |
| 3 | <>0              | ""                | -1    |
| 4 | >0               | OK – Losses       | 0     |
| 5 | <0               | OK – Losses       | 1     |
| 6 | <0               | OK – New Sales    | 0     |
| 7 | >0               | OK – New Sales    | 1     |
+---+------------------+-------------------+-------+

• Column A: Lookup Condition
• Column B: Lookup string
• Column C: Return value

Data example with correct hard coded output (column C):

+---+------------------+-------------------+-------+
|   |        A         |         B         |   C   |
+---+------------------+-------------------+-------+
| 1 | Current to Prior | Portfolio comment | Error |
| 2 | 0                |                   | 1     |
| 3 | -100             | OK – Losses       | 1     |
| 4 | 50               |                   | -1    |
| 5 | 200              | OK – Losses       | 0     |
| 6 | 0                |                   | 1     |
| 7 | -400             | OK – New Sales    | 0     |
| 8 | 0                |                   | 1     |
+---+------------------+-------------------+-------+

• Column A: Data value
• Column B: Data string
• Column C: Output formula

I need a formula that matches the data value with the lookup condition, the data string with the lookup string and outputs the return value.

Answer 1

I know you weren't necessarily asking for a VBA solution, but myself (and many others) prefer using UDFs as, in my opinion, it makes reading formulas easier and cleaner - plus you can do without the helper cells.

We start off your UDF by creating a Select Case Statement. We could choose to use either the Numerical Value or String for the cases. I decided to go with the string.

Within each case, you will compare the numerical values provided to the lngCondition parameter, which will ultimately return the value to the function.

Since you didn't have any cases for when textual values could have a lngCondition = 0, I made it return a worksheet error code #VALUE, just as you'd expect from any other Excel formula. This is the reason for the UDF having a variant return type.

Public Function ReturnErrorCode(lngCondition As Long, strComment As String) As Variant

    Select Case strComment
    Case ""
        If lngCondition = 0 Then
            ReturnErrorCode = 1
        Else
            ReturnErrorCode = -1
        End If

    Case "OK - Losses"
        If lngCondition > 0 Then
            ReturnErrorCode = 0
        ElseIf lngCondition < 0 Then
            ReturnErrorCode = 1
        Else
            ' Your conditions don't specify that 'OK - Losses'
            '     can have a 0 value
            ReturnErrorCode = CVErr(xlErrValue)
        End If

    Case "OK - New Sales"
        If lngCondition < 0 Then
            ReturnErrorCode = 0
        ElseIf lngCondition > 0 Then
            ReturnErrorCode = 1
        Else
            ' Your conditions don't specify that 'OK - New Sales'
            '     can have a 0 value
            ReturnErrorCode = CVErr(xlErrValue)
        End If

    Case Else
        ReturnErrorCode = CVErr(xlErrValue)

    End Select

End Function

You would then use this formula in the worksheet as such:

=ReturnErrorCode(A1, B1)

---

Great! But I have no knowledge of VBA and don't know how to add a UDF.

• First, you need to open the VBA Editor. You can do this by simultaneously pressing Alt + F11.
• Next, you need to create a standard code module. In the VBE, click Insert then select Module (NOT Class module!).
• Then copy the code above, and paste it into the new code module you just created.
• Since you have now added VBA code to your workbook, you now need to save it as a macro-enabled workbook the next time you save.