Lookup for a value in a range that has array values and constants mixed within

Question

I was provided data that has an id column that leads to a value.

When two ids lead to the same value they are contained in an array ={...}, otherwise, they are a constant value.

ID	VALUE
0001	A
=@{0002,0003}	B
0004	C

Looking for the value attributed to the ID 0001 is a straightforward LOOKUP function.
I cannot think of a function that will return the value attributed to an ID contained within an array (i.e. ID 0003).

I'm not allowed to create a new row for every value within the array.

=IFERROR(LOOKUP(VALUE([@CCC]),'Sheet1'!$B$4:$C$541),[Some way of looking within the ids contained within the array])

Public Function IsInArray(stringToBeFound As Integer, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False

End Function

Answer 1

As far as I know, Microsoft does not provide a way to get the array in a cell as array. If you access the Value-property, you will get the first entry of the array (that's also what is displayed in Excel), so in your case 2.

Best bet is probably to access the array as string using the Formula2-property and do some string gymnastics to create an array out of it.

I played around with it and came up with the following code. I tried to implement handling of strings and numbers. If a cell contains only a single value, it is converted to an array with only one member.

Function myArrayLookup(searchValue As Variant, lookupRange As Range, col_index As Long) As Variant
    Dim cell As Range
    
    For Each cell In Intersect(lookupRange, lookupRange.Parent.UsedRange)
        Dim cellValue As Variant
        cellValue = cell.Formula2
        
        Dim tokens() As String
        If Left(cellValue, 3) = "=@{" Then
            cellValue = Mid(cellValue, 4, Len(cellValue) - 4)
            tokens = Split(cellValue, ",")
        Else  ' We deal with a single value
            ReDim tokens(0 To 0)  
            tokens(0) = cell.value
        End If
    
        Dim i As Long
        For i = 0 To UBound(tokens)
            If VarType(searchValue) = vbDouble Then
                ' Numeric comparison
                If Val(searchValue) = Val(tokens(i)) Then
                    myArrayLookup = cell.Offset(0, col_index - 1)
                    Exit Function
                End If
            Else
                ' String comparison
                If searchValue = Replace(tokens(i), """", "") Then
                    myArrayLookup = cell.Offset(0, col_index - 1)
                    Exit Function
                End If
            End If
        Next i
    Next cell
    myArrayLookup = CVErr(xlErrValue)
End Function

You can use it as an UDF or within VBA. Usage is very similar to VLookup (only without the infamous 4th parameter).

If someone tells us a better way to deal with Excel-arrays in VBA, I am glad to upvote (and possibly delete my answer).