Not with a built-in formula. You could return the fill color using a
User Defined Function (UDF), but you couldn't use it to color the
calling cell:
Public Function VLookupColor(Lookup_value As Variant, _
table_array As Range, col_indexnum As Integer, _
Optional range_lookup As Integer = 1) As Variant
Dim found As Variant
On Error Resume Next
found = Application.Match(Lookup_value, _
table_array.Resize(, 1), range_lookup)
If IsError(found) Then
VLookupColor = CVErr(xlErrNA)
Else
VLookupColor = table_array( _
found, col_indexnum).Interior.ColorIndex
End If
On Error GoTo 0
End Function
(note: there's no error checking other than for a non-match).
If you wanted to color the cell, you'd have to use an event macro.
Assume A1 is your lookup value, J1:K100 is your table and you want
an exact match:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim found As Variant
If Not Intersect(Range("A1,J1:J100"), Target) Is Nothing Then
On Error Resume Next
found = Application.Match( _
Range("A1").Value, Range("J1:J100"), 0)
If IsError(found) Then
Range("B1").Interior.ColorIndex = xlColorIndexNone
Else
Range("B1").Interior.ColorIndex = _
Range("J1")(found, 2).Interior.ColorIndex
End If
End If
End Sub
Put this in the worksheet code module (right-click on the worksheet
tab, select View Code, and paste into the window that opens):
