Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.