Table of Contents
The VLOOKUP function in Excel is a powerful tool that allows users to search for and retrieve data from a specific column in a table. When working with numbers that are stored as text, the VLOOKUP function can still be utilized by converting the numbers to text format. This can be done by using the TEXT function or by adding an apostrophe before the number. By using the VLOOKUP function with numbers stored as text, users can easily access and analyze data without having to manually convert the numbers. This can save time and improve the accuracy of data analysis in Excel.
Excel: Use VLOOKUP with Numbers Stored as Text
You can use the following syntax to use a VLOOKUP function in Excel to look up a number that is stored as a text in a range where the numbers are stored as ordinary numbers:
=VLOOKUP(VALUE(E1), A2:B11, 2, FALSE)
This particular formula looks up the number in cell E1 (in which this value is saved as text) in the range A2:B11 and returns the corresponding value in the second column of the range.
Note that this formula uses the VALUE function to convert a text string that represents a number into a numeric value.
The following example shows how to use this syntax in practice.
Example: How to Use VLOOKUP with Numbers Stored as Text
Suppose we have the following dataset in Excel that contains information about the sales made by various employees at some company:
Now suppose that we would like to look up the employee ID 3490 and return the corresponding sales value.
However, suppose this ID value is stored as Text in Excel.
Suppose we attempt to use the following VLOOKUP function to look up this value:
=VLOOKUP(E1, A2:B11, 2, FALSE)
The following screenshot shows how to use this formula in practice:
We receive #N/A as a result because the value in cell E1 is stored as Text.
To get around this issue, we must use the VALUE function within the VLOOKUP function:
=VLOOKUP(VALUE(E1), A2:B11, 2, FALSE)
The formula correctly returns a value of 22, which matches the sales value associated with employee ID 3490.
If you have the opposite scenario where the lookup value is stored as a number but the numbers in the range are stored as text, you can use the following formula instead:
=VLOOKUP(TEXT(E1,0), A2:B11, 2, FALSE)
The TEXT function converts the lookup value to a text value and then performs the VLOOKUP.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I utilize the VLOOKUP function in Excel when working with numbers stored as text?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-utilize-the-vlookup-function-in-excel-when-working-with-numbers-stored-as-text/
stats writer. "How can I utilize the VLOOKUP function in Excel when working with numbers stored as text?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-utilize-the-vlookup-function-in-excel-when-working-with-numbers-stored-as-text/.
stats writer. "How can I utilize the VLOOKUP function in Excel when working with numbers stored as text?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-utilize-the-vlookup-function-in-excel-when-working-with-numbers-stored-as-text/.
stats writer (2024) 'How can I utilize the VLOOKUP function in Excel when working with numbers stored as text?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-utilize-the-vlookup-function-in-excel-when-working-with-numbers-stored-as-text/.
[1] stats writer, "How can I utilize the VLOOKUP function in Excel when working with numbers stored as text?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I utilize the VLOOKUP function in Excel when working with numbers stored as text?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.