Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

I have this:

enter image description here

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?

share|improve this question
up vote 4 down vote accepted

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

share|improve this answer
1  
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  
+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.

share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .