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.

Please Login or Register  to view this content.

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