How to Use VLOOKUP to Map Text to Numbers

Time and again, we have to map or decipher text inputs into arbitrary numeric values. This may appear to be an easy task if we have to do this for a small set of text values, but if we have to keep a record of a hefty amount of text data, then this task often becomes problematic. In such situations, VLOOKUP function comes to your rescue.

The VLOOKUP Function in Excel

This is an inbuilt function of Excel. Each and every time a VLOOKUP function is called, it seeks for a lookup value. The ‘V’ refers to the vertical search of a VLOOKUP function. The lookup value is most of the times the leftmost column of any section which is called a table array in a spreadsheet. The function has various applications and mapping text to numbers is one of them.

VLOOKUP FOR MAPPING TEXT TO NUMBER

In order to map text to number, we use the formula as shown:

=VLOOKUP(text,lookup_table,column,0)

Example of VLOOKUP to Map Text to Numbers

To use the above formula, the first step is mapping the text values /statuses to arbitrary numeric values. In the example shown, we create a lookup table that contains the name of shapes in a column and their sides in another as shown.

Figure 1: Lookup table

Now, the user can map the numeric value (sides) of any of the shapes by simply entering the shape and then VLOOKUP function can be called to display the numeric value.  In this case, we’ll enter the formula in F8 as shown:

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

Figure 2: Illustration of text to numeric mapping using VLOOKUP

The value in cell F7 is used by the formula for a lookup value in the range B6:C11.

`Here ‘2’ refers to the second column of the table and ‘0’ is the last argument to force an accurate match. In this way, we are mapping text values to numeric outputs. Similarly, we can handle text to text and number to text mapping.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar