Forum Discussion

jurkasovak's avatar
jurkasovak
Copper Contributor
Jan 27, 2023
Solved

xlookup with format

Hi!

 

How I can use function xlookup with format.

For example If I want find cell with color...

 

Thanks.

excel
  • HansVogelaar's avatar
    HansVogelaar
    Jan 30, 2023

    jurkasovak 

    Here is a simplistic function.

    Function XLookupColor(ColorCell As Range, LookupRange As Range, _
        ResultRange As Range, IfNotFound As Variant) As Variant
    Dim LookupColor As Long
    Dim i As Long
    Application.Volatile
    LookupColor = ColorCell.Interior.Color
    For i = 1 To LookupRange.Count
        If LookupRange(i).Interior.Color = LookupColor Then
            XLookupColor = ResultRange(i).Value
            Exit Function
        End If
    Next i
    XLookupColor = IfNotFound
End Function

    The function has four arguments:

    1. ColorCell: a cell with the fill color to search for.
    2. LookupRange: the range with colored cells.
    3. ResultRange: the range with values to return. This must be the same size as the lookup range.
    4. IfNotFound: the value to return if the color is not found. Use "" if you want to return a blank.

    Warning: the result will not be updated automatically if you change the color of the color cell or the color range. Press F9 to force a recalculation.

    Sample workbook attached. You'll have to allow macros when you open it.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 27, 2023

    jurkasovak 

    Excel's lookup functions can only search for a cell's value, not its formatting.

    If the lookup range has been colored using conditional formatting, you should use the conditions of the conditional formatting rules, Otherwise, you'd need VBA code.

    • jurkasovak's avatar
      jurkasovak
      Copper Contributor
      Jan 30, 2023
      Hi Hans!

      Thank you for answer. Unfortunately, I cannot use conditional formatting, because the source file does not have data that is colored according to the rule (my colleague marks the status of the order according to her own color).
      Do you have VBA code?

      Thanks, Katka.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 30, 2023

        jurkasovak 

        Here is a simplistic function.

        Function XLookupColor(ColorCell As Range, LookupRange As Range, _
        ResultRange As Range, IfNotFound As Variant) As Variant
    Dim LookupColor As Long
    Dim i As Long
    Application.Volatile
    LookupColor = ColorCell.Interior.Color
    For i = 1 To LookupRange.Count
        If LookupRange(i).Interior.Color = LookupColor Then
            XLookupColor = ResultRange(i).Value
            Exit Function
        End If
    Next i
    XLookupColor = IfNotFound
End Function

        The function has four arguments:

        1. ColorCell: a cell with the fill color to search for.
        2. LookupRange: the range with colored cells.
        3. ResultRange: the range with values to return. This must be the same size as the lookup range.
        4. IfNotFound: the value to return if the color is not found. Use "" if you want to return a blank.

        Warning: the result will not be updated automatically if you change the color of the color cell or the color range. Press F9 to force a recalculation.

        Sample workbook attached. You'll have to allow macros when you open it.

Resources