Excel: VLookUp solves a potential Nested IF problem

Question

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

Answer 1

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!