1. ## 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.

2. 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```

3. 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.

4. I like 1E6
A million is enough for me.

zeddy

5. Well I could have gone for 32768 but I think 1E+100 looks much more scientific...

6. 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
•