< Go Back

Map text to numbers

Time and again, we have to map or decipher text inputs into arbitrary numeric values. This may look 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.

VLOOKUP FUNCTION

This is an inbuilt function of excel. Each and every time a VLOOKUP function is called in Excel, 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

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.

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
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar