# What function should I use in Excel for searching a (multiple) text string?

The title is a bit unclear, but I'll be explaining it now for better clarity.

I have this:

When I type in the Input field, I'd like Excel to show me the result in the Output field. For example, if I write Four, I'd like it to output 20, or if I write one of the other three words, then 12. The problem is that... I can't make it to work.

The formula I tried is "=CERCA(C2;G:G;H:H)" (cerca means search), so I'm saying "Take what I write in the cell C2, search through the column G and give me what you find from the column H", but the result is always N.D. (Not available). I've tried other combinations and:

• Text strings, does not work;
• Single numbers, works (if I search 1, it says 2, which is what I expect);
• multiple numbers, does not work (if I search 4, nothing happens).

What function should I use?

-

## 2 Answers

Try this:

``````=IFERROR(VLOOKUP(C2;G:H;2;0);VLOOKUP("*"&C2&"*";G:H;2;0))
``````

First it searches for an exact match (when you type `Four` into the search box, for example).

If it does not find one then it uses VLOOKUP with wildcard *. For example, you can search for `our` and it will return value 20

-
I submit to the awesomeness of your VLOOKUP skills – Rhys Gibson Oct 3 '12 at 9:30
@Siim K, does it work if you search "one"? Or "2"? Thank you for your effort. :) – Alenanno Oct 3 '12 at 9:48
@Alenanno - sure, it works with your examples. I suggest you try it out yourself with different search terms :) Just keep in mind that at the moment it is case INsensitive. So `four` is not different from `Four` – Siim K Oct 3 '12 at 9:52
@SiimK Wonderful! Well, I don't think the case sensitiveness will be a problem. I'll try it out and let you know. You see, with Excel in italian, I need to translate the formulas you just used. ¬_¬ I wish I could just paste eheh :) – Alenanno Oct 3 '12 at 9:55
+1, Note that you could get some mismatches if C2 = "six" and no six exists in lookup range.....but sixteen or sixty does, in which case this formula should work without false matches: `=LOOKUP(2^15,SEARCH(","&C2&",",","&G2:G5&","),H2:H5)` – barry houdini Oct 3 '12 at 18:12

One way is to only have 1 possible value per cell in G and then use:

``````=VLOOKUP(C2,G1:H10,2)
``````

The way you have written your SEARCH is all wrong as it doesn't take ranges in the 2nd and 3rd arguments.

-
So, there is not a way for me to have also multiple values in cells under G? – Alenanno Oct 3 '12 at 8:53
only for 1 cell. i.e. SEARCH only works on a single cell and within that cell – Rhys Gibson Oct 3 '12 at 9:12