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.

**Table of Contents**hide

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

**📌**** 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**.

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.

**Read More:** **INDEX MATCH vs VLOOKUP Function (9 Examples)**

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****Excel LOOKUP vs VLOOKUP: With 3 Examples****Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)****How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)**

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

**📌**** 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.

**📌**** Step 2:**

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

➤ Press **Finish **and you’re done.

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.

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

**Read More:** **Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)**

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

**Read More: ****VLOOKUP to Find Duplicates in Two Columns (2 Ways)**

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

**Read More:** **VLOOKUP and Return All Matches in Excel (7 Ways)**

**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**

**Excel VLOOKUP to Return Multiple Values Vertically****Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel****VLOOKUP to Search Text in Excel (4 Easy Ways)****VLOOKUP with Drop Down List in Excel****VLOOKUP Formula in Excel with Multiple Sheets (4 Simple Tips)****Excel VLOOKUP to Find Last Value in Column (with Alternatives)**