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.
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:
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:
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.