Assign Number Value to Text – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to assign a numerical value to a text string.

assign number value to text Main


To assign a number value to a text string, we can use the VLOOKUP Function along with our lookup table. In this example, we assign a SKU number to each product like so:



This formula finds “Chair” in the range E3:F5 and returns the second column in that row: “2235”. (The “0” argument specifies that we need an exact match.)


Another method is to use the XLOOKUP Function to achieve the same results:



Instead of a range and column index number, the XLOOKUP Function takes a lookup array and a return array.

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:


But these formulas will not work properly when copy and pasted elsewhere within your Excel file. Instead, you should use locked cell references like this:


Read our article on Locking Cell References to learn more.

Assign Number Value to Text in Google Sheets

The examples shown above work the same way in Google Sheets as they do in Excel.

assign number value to text Google

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List