VLOOKUP with Numbers in Excel (4 Examples)

The VLOOKUP function is generally used to look for a value in the leftmost column in a table and the function will return a value in the same row from the specified column. In this article, you will learn how you can use this VLOOKUP function to look up numbers under different criteria with appropriate illustrations.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


2 Criteria of Using VLOOKUP with Numbers in Excel

1. Basic Example of Applying VLOOKUP Function with Numbers

In the following table in the picture, several data containing the order details of different smartphone products have been recorded. In the output table at the bottom, we have to extract all data available from the table based on an order ID.

Basic Example of Applying VLOOKUP Function with Numbers

📌 Step 1:

➤ Select the first output Cell E17 and type the following formula with the VLOOKUP function:

=VLOOKUP($E$16,$B$5:$G$14,MATCH(D17,$B$4:$G$4,0),FALSE)

➤ Now press Enter and you’ll find the name of the customer whose order ID is 1034.

Basic Example of Applying VLOOKUP Function with Numbers

In this formula, the MATCH function has been used to define the column number of the VLOOKUP function for a particular output type.

📌 Step 2:

➤ Use Fill Handle now to autofill the other cells ranging from E18 to E21.

And you’ll get all available data from the table based on the specified order ID at once.

Basic Example of Applying VLOOKUP Function with Numbers


2. VLOOKUP with Numbers Formatted As Text in Excel

i. Use of Text to Columns Command

Sometimes our data table may contain numbers in text format. In that case, the previously used formula won’t work out and it will return a #N/A error as shown in the picture below. So, here we have to change the format of the ID numbers present in Column B.

VLOOKUP with Numbers Formatted As Text

📌 Step 1:

➤ Select the range of cells B5:B14 containing the order IDs first.

➤ Under the Data ribbon, select the Text to Columns command from the Data Tools drop-down.

A wizard box will open up.

VLOOKUP with Numbers Formatted As Text

📌 Step 2:

➤ In the dialogue box, select the data type as delimited.

➤ Press Finish and you’re done.

VLOOKUP with Numbers Formatted As Text

The delimiters found with the numbers will be removed now and you’ll find your IDs in number format. The previously used formula in the first output Cell E17 will now show the actual data based on the selected ID.

📌 Step 3:

➤ Now autofill the other output cells (E18:E21) like before to get all other available data for that selected order ID.

VLOOKUP with Numbers Formatted As Text

Eventually, you’ll find all the expected data as shown in the screenshot below.

VLOOKUP with Numbers Formatted As Text


ii. Use of TEXT function with VLOOKUP

We have another option to look up an order ID in the range of cells formatted as text. We have to use the TEXT function to define the lookup_value argument in the VLOOKUP function. Thus the selected order ID number will be converted into the text format and then we’ll use this text formatted lookup value to find its duplicate in Column B.

So, the required formula in the output Cell E17 will be:

=VLOOKUP(TEXT($E$16,0),$B$5:$G$14,MATCH(D17,$B$4:$G$4,0),FALSE)

After pressing Enter and auto-filling the rest of the output cells, you’ll get all available data for the selected order ID right away.

VLOOKUP with Numbers Formatted As Text


iii. Use of VALUE Function with VLOOKUP

In the last section, let’s think of an opposite case where the lookup value is in text format but the order IDs in the table are in number format. Now, we have to use the VALUE function to convert the lookup value from the text format into a number format.

In the following table, the lookup order ID in Cell E16 is in text format. So, in the first output Cell E17, while applying the VALUE function to define the lookup value, the VLOOKUP function will look like this:

=VLOOKUP(VALUE($E$16),$B$5:$G$14,MATCH(D17,$B$4:$G$4,0),FALSE)

After pressing Enter and auto-filling the rest of the output cells like before, you’ll find all the return values right away.

VLOOKUP with Numbers Formatted As Text


Concluding Words

I hope all the examples under the different criteria described above will now help you to apply them in your Excel spreadsheets while using the VLOOKUP function with numbers. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

VLOOKUP with Drop Down List in Excel

Range Lookup with VLOOKUP in Excel (5 Examples)

10 Best Practices with VLOOKUP in Excel

VLOOKUP to Return Multiple Values Horizontally in Excel

How to Use VLOOKUP If Cell Contains a Word within Text in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo