Function in a cell to search for a text

Hi,

Pl do help me on this:

I have entered the names of places falling under category "X" in column A , category "Y" in column B and category "Z" in column C of sheet 1 in excel. Now in sheet 2 column B, I enter the name of a place. In the first cell of column C, I want to write a formula where in it should check if the place entered in sheet 2 column B falls under any of the categories X, Y, Z of sheet 1, and it should display X,Y, Z accordingly or it should display just "Others".

I need the formula for this so that if it is written in one cell it can be dragged down to all the cells of column C in sheet 2. Note that I am entering the names of places ie. they are texts and not numbers.

Looking forward to replies.

Hi,

Can you attach an example of what you neeed?

Dave

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

I entered the codes and also typed the formula in the cell but am getting an error "Name" in the cell

Actually where should I enter the codes after opening the excel sheet?

Let me be more specific by giving you the details:

1) In sheet 1, I have the following data for eg:

Metropolis Urban Semi-Urban

Delhi Hyderabad Adilabad
Mumbai Vijayawada Kagaznagar
Kolkata Vishakapatnam Nirmal
Chennai Guntur Bellampalle
Guwahati Mandamarri
Patna Mancherial
Chandigarh Nizamabad
Durg-Bhilai Bodhan
Nagar Kamareddy
Raipur Ramagundam
Ahmadabad Jagtial

The category names is given in the first row.ie Metropolis,etc.

Now in sheet 2 column D cell 2 i will enter the name of the place. In column K , I want this formula such that the place entered in cell 2 of Column D sheet 2 will be categorised into either Metro, Urban, semiurban and if the place does not fall in any of these categories it should display "rural"

Pl do tell me specifically what I should do. Thanks a lot for all the effort once again. Looking forward to your help.

Lilly

Hello Starguy,

That was great! It is simple and really works. I am extremely grateful to you.

May I ask you for some more help with regard to VBA codes and programming?

Pl do let me know.

Lilly