Hello Lilly,

This macro is a User Defined Function. It works like a regular Worksheet function. It determines the length of columns A to C on the specified worksheet. The category search columns can not be on the same worksheet as the search term.

Copy this code. After you have inserted a Standard VBA Module into your Workbook, paste the code into it.

Example:
Sheet2 "B1" = "London"
Sheet2 "C1" Formula: =FindCategory(B1, 'Sheet1'!A:C)
C1 will display what categories matched.

Public Function FindCategory(Place_Name As Range, Category_Columns As Range)

  Dim Cat As String
  Dim CatA As String
  Dim CatB As String
  Dim CatC As String
  Dim LR(2) As Long
  Dim LastRow As Long
  Dim Wks As Worksheet
  Dim WksName As String

  'Udpate when any linked cells are updated or sheet calculates
    Application.Volatile

    WksName = [Category_Columns].Parent.Name
      If WksName = ActiveSheet.Name Then
         MsgBox "Category columns must be on a different sheet." 
         FindCategory = ""
         Exit Funtion
      End If

    Set Wks = Worksheets(WksName)
    LR(0) = Wks.Cells(Rows.Count, "A").End(xlUp).Row
    LR(1) = Wks.Cells(Rows.Count, "B").End(xlUp).Row
    LR(2) = Wks.Cells(Rows.Count, "C").End(xlUp).Row

    'Find the longest row of the 3 columns
      LastRow = ActiveSheet.WorksheetFunction.Max(LR(0), LR(1), LR(2))

      For R = 1 To LastRow
        If Wks.Cells(R, "A") = Place_Name Then CatA = "X,"
        If Wks.Cells(R, "B") = Place_Name Then CatB = "Y,"
        If Wks.Cells(R, "C") = Place_Name Then CatC = "Z,"
      Next R

      If CatA <> "" Then Cat = Cat & CatA
      If CatB <> "" Then Cat = Cat & CatB
      If CatC <> "" Then Cat = Cat & CatC
      If Cat = "" Then 
         Cat = "Other"
      Else
         Cat = Left(Cat, Len(Cat) - 1)
      End If

      FindCategory = Cat

End Function
Sincerely,
Leith Ross