VBA for Nested custom function

[flawrence3101]

I am trying to create a custom function. I want the program to loop through all the entries in column A. The formula is:
=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(Keyword, Description)),0))

The table is:
A B C
Description Keyword Category
Kroger store #1 Kroger Food
Exxon store #2 Exxon Auto
CVS store 3 Healthcare Prescriptions

The list of descriptions is around 240 rows. Each column is a named range.

Any help would be appreciated.

 

[flawrence3101]

I do not think I explained what I am looking to do very well. Here is an example.

In Sheet #1 There is a list of Descriptions.
Description Category
Kroger store #1
Sprouts store #2
Exxon store #3
Rudy's store # 321
CVS store #345

In Sheet #2 there is a column Named Keyword and another column Named Category.

Keyword Category
Kroger Food
Sprouts Food
Exxon Auto
Rudy's Food
CVS Healthcare

I want to use a custom function that will have this formula:
=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(Keyword,Sheet3!A2)),0))

I need it to return what result and enter it in Sheet #1 next to the matching Description and under the Category column. I want it to loop through all of the Sheet #1 Descriptions and return the Category automatically.

 

[Joe4]

Welcome to the Board!

I have merged your two threads together. In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will typically be locked or deleted.

 

[Scott Huish]

I don't know what Sheet3 in your formula references, but it looks like you could just use a VLOOKUP:

=VLOOKUP(TEXTBEFORE(A2," "),Sheet2!A:B,2,0)
Copy down.

Why do you need VBA?