Excel formula: Map text to numbers

Generic formula

=VLOOKUP(text,lookup_table,2,0)

Explanation

To map or translate text inputs to arbitrary numeric values, you can use the VLOOKUP function with a simple table.

In the example, we need to map five text values (statuses) to numeric status codes as follows:

Status	Code
Started	10
Normal	20
Delayed	30
Complete	40
Cancelled	50

Since there is no way to derive the output (i.e. it's arbitrary), we need to do some kind of lookup. The VLOOKUP function provides an easy way to do this.

In the example shown, the formula in F8 is:

=VLOOKUP(F7,B6:C10,2,0)

How this formula works

This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate "2nd column", and zero as the last argument to force an exact match.

Although in this case we are mapping text values to numeric outputs, the same formula can handle text to text, or numbers to text.

Author

Dave Bruns

Related formulas

Calculate grades with VLOOKUP

To assign a grade based on a score, you can use the VLOOKUP function configured to perform an approximate match. In the example shown, "key" is the named range B5:C9, the formula in F5. copied down, is: = VLOOKUP ( E5 , key , 2 , TRUE ) where "key"...

Get employee information with VLOOKUP

If you want to retrieve employee information from a table, and the table contains a unique id to the left of the information you want to retrieve, you can easily do so with the VLOOKUP function. In the example shown, the VLOOKUP formula looks like...

Two-way lookup with VLOOKUP

Preface Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create...

Merge tables with VLOOKUP

To merge tables, you can use the VLOOKUP function to lookup and retrieve data from one table to the other. To use VLOOKUP this way, both tables must share a common id or key. This article explains how join tables using VLOOKUP and a calculated...

VLOOKUP without #N/A error

To hide the #N/A error that VLOOKUP throws when it can't find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP can't find a value in a lookup table, it returns the #N/A...

Sum text values like numbers

To translate text values into numbers and sum the result, you can use an INDEX and MATCH formula, and the SUM function. In the example shown, the formula in H5 is: { = SUM ( INDEX ( value , N ( IF ( 1 , MATCH ( C5:G5 , code , 0 ))))) } where "code"...

Map inputs to arbitrary values

If you need to map or translate inputs to arbitrary values, you can use the VLOOKUP function. In the example, we need to map the numbers 1-6 as follows: Input Output 1 10 2 81 3 17 4 23 5 13 6 31 Since there is no way to derive the output (i.e. it's...

Related functions

Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.