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,754
    Thanks
    129
    Thanked 692 Times in 628 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 18:37. Reason: added file

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,300
    Thanks
    3
    Thanked 204 Times in 188 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
    3,128
    Thanks
    149
    Thanked 573 Times in 545 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,300
    Thanks
    3
    Thanked 204 Times in 188 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
  •