Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ISTEXT and VLOOKUP

    For experienced Excel users this must be easy. Please be so kind as to take a look to the attached spreadsheet and help to use ISTEXT and VLOOKUP in the example. What I try to obtain is indicated in the spreadsheet. Thanks so much.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    rmomene,

    Here is a User Defined Function (UDF) that will do what you want. It will do a search with your keywords on the expression specified by the cell in the formula. If one is found then it will reference the correct brand. In cell B2 enter the formula =Key(E2) then copy down.

    HTH,
    Maud

    rmomene2.png


    Code:
    Public Function Key(rng As Range) As String
    Application.Volatile
    '----------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim kw, I As Integer, num As Integer
    kw = Array("Fineherbs", "Daylight", "Sunlight", "Amazing")
    '----------------------------------------------
    'TEST FOR KEYWORD, ASSIGN BRAND
    For I = 0 To 3
        num = InStr(1, rng, kw(I), 1)
        If num <> 0 Then
            Select Case I
                Case 0
                    Key = "Fineherbs Special Selection"
                Case 1
                    Key = "Daylight Centenary"
                Case 2
                    Key = "Sunlight Annyversary"
                Case 3
                    Key = "Amazing Treats"
            End Select
            Exit Function
        Else:
            Key = ""
        End If
    Next I
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-19 at 19:37. Reason: added file

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Change column H so it only contains the keywords, then use:
    =LOOKUP(1E+100,SEARCH($H$14:$H$17,E2),$I$14:$I$17)

    See attached file.

    @Maud: there's no need to make that UDF volatile.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    I like 1E6
    A million is enough for me.

    zeddy

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Well I could have gone for 32768 but I think 1E+100 looks much more scientific...
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I love both of them. Thanks so much!!!
    Problem solved.
    All the best. R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •