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.

I manually enter numbers on one cell according to text values in the cell adjacent to it. Is there a way to use the IF function to help me manage this? The text is automatically generated with a report but I put the numbers in manually in Excel.

Example of my weekly boredom below:

number    Text in Cell
3         Order A
3         Order A 
1         Order C
2         Order B
3         Order A
1         Order C
2         Order B
2         Order B

HELP! My eyes and soul hurt each time I need to do this. Thanks Mike

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

You can use VLOOKUP instead

In another sheet have a table of the text values and their corresponding numbers, ordered by the lookup value (that bit is important!):

Order A          3
Order B          2
Order C          1

You can then use VLOOKUP(column_with_generated_text, lookup_table, 2)

The 2 means it will return the 2nd column value from the lookup table, i.e. the number.

This way, if you get more text/number pairs to be added you need only add them to the lookup and not have to change complex IF statements each time.

EDIT: This is what I did on my test XLS...

Sheet 1:- 2 columns, A1:B5:

Commissioned-1st activity   5
Commissioned-2nd activity   4
First response - write     1
Workplan to write           2
Workplan-company to agree   3

I then defined a Named Range for A1:B5 as "lookup" (without quotes)

Sheet 2:- Column A1:A5:

First response - write
Workplan to write
Workplan-company to agree
Commissioned-2nd activity
Commissioned-1st activity

Then in column B1:B5 the formulas:

=VLOOKUP(A1,lookup,2,FALSE)
=VLOOKUP(A2,lookup,2,FALSE)
=VLOOKUP(A3,lookup,2,FALSE)
=VLOOKUP(A4,lookup,2,FALSE)
=VLOOKUP(A5,lookup,2,FALSE)

This gives me a column B1:B5 of:

1
2
3
4
5

Hope that makes sense!

share|improve this answer
3  
They don't need to be sorted if you add the "FALSE" parameter at the end. Which would instead make the formula "VLOOKUP(column_with_generated_text, lookup_table, 2, FALSE)". This means that it searches for an exact match, rather than just stopping on what it considers 'closest'. –  Margaret Mar 15 '10 at 11:44
    
Ah, thanks Margaret - I've learnt something new as well! –  Shevek Mar 15 '10 at 11:45
    
I've written the formula and, well it gives me room for thought. It isn't doing what i thought it would. My Generated text is in Column A and my values are in Column B. My generated text is in Alphabetical order, which means my Values are not: 5, 4, 1, 2, 3. When I drag the formula down in to the cells below it just starts making it up as it goes along. Any ideas? And after 4th cell (there are 5 rows in my table) I get the #Value error. –  RocketGoal Mar 15 '10 at 11:55
    
I tried adding false and the results are the same. The text has 'hyphens' in it; does this cause a problem? –  RocketGoal Mar 15 '10 at 11:57
    
try giving the lookup table a Named Range and using this in the VLOOKUP formula –  Shevek Mar 15 '10 at 12:05
show 5 more comments

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.