Excel Formula Help Needed Please 1

[arvarr]

Hi
I need help with a formula and its doing my head in.

I have attached sample file.

On Sheet2, i am hoping for the formula to pull through the results from the Categories worksheet.

Thanks in advance for your help,
Arv

 

[SkipVought]

Your unique key phrases:

Microsoft-Test
Microsoft invoice
Microsoft - Test

BTW, for those of us traveling lite, (on my iPhone) how about posting your formula?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Hi Skip
Sure, here is the formula.
=INDEX(Categories!$A$1:$C$5,MATCH(INDEX(Categories!$B$2:$B$5,_xlfn.AGGREGATE(15,6,ROW(Categories!$A$1:$C$5)/(ISNUMBER(FIND(Categories!$B$2:$B$5,A3))),1)),Categories!$B$2:$B$5,0),3)

I need the formula to find the exact match from the text that i keyed into the keywords column.
The description may have longer length but there would be keywords that i entered into the keywords column to associate with a category. Hope that makes sense.

Thanks,
Arv

 

[arvarr]

Is there a formula that can tackle this issue?
Thanks.

 

[SkipVought]

Why do you even have Description in your Categories sheet?
[pre]
Keywords Category
Invoice Microsoft-Test General
Microsoft Invoice Hardware
Invoice for Microsoft - Test Software
[/pre]
Put this code in a MODULE.

Save your workbook as a Macro-Enabled Workbook.

Use this user-defined function on your sheet like any other function.
[tt]
C2: =findcat(A2)
[/tt]

Function FindCat(sDes As String)
    Dim rKey As Range, xl As Application
    
    Set xl = Application
    
    FindCat = "NONE"
    
    For Each rKey In [Keywords]
        If Not IsError(xl.Find(rKey.Value, sDes)) Then
            FindCat = Intersect(rKey.EntireRow, [Category]).Value
            Exit For
        End If
    Next
    
    Set xl = Nothing
End Function

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

HI Skip
Not sure if i am missing anything.
Copied the code and saved as .xlsm.
Tried the function by itself C2: =findcat(A2) and it returned #value

And, how do i incorporate the findcat function into my formula?

Thanks,
Arv

 

[SkipVought]

=findcat(A2) is all you need in row 2.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Sorry, still getting #value.
Do I need to name range for keywords?
Thanks.

 

[arvarr]

Tried naming range.
It gives me the results but if I add prefix or suffix in description, it gives incorrect output.
Pls help.

 

[SkipVought]

Where was my head! So very sorry.

I named the ranges in your Categories sheet based on the Headings in the table.

Attached is my workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Works like a charm.
Thanks Skip. Since Day 1 i joined as a member, you've never failed to provide a solution to my questions.
Greatly appreciate all your help.

 

[Andrzejek]

...yet you only gave 1 star for any help received...


---- Andy

There is a great need for a sarcasm font.

 

[arvarr]

Hi Andy
How do I give more stars?
Happy to give add many stars as I can.
Thanks,
Arv

 

[Andrzejek]

It looks to me you figure it out - just click on [blue]Great Post![/blue] link in the helpful post(s)


---- Andy

There is a great need for a sarcasm font.