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 return a value in the same row from another specified column. In this article, we will demonstrate how to use the VLOOKUP function to look up numbers under different criteria.

In the following table are the order details of different smartphone products. In the output table at the bottom, we’ll extract all data available from the table based on an order ID.

Example 1 – Basic Example of Using the VLOOKUP Function with Numbers

Steps:

In the first output cell E17, enter the following formula:

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

Press Enter to return the name of the customer whose order ID is 1034.

In this formula, the MATCH function defines the column number of the VLOOKUP function for a particular output type.

Use the Fill Handle to autofill from E18 to E21.

All the available data from the table based on the specified order ID is returned at once.

Example 2 – Using VLOOKUP with Numbers Formatted As Text

2.1 – Using the Text to Columns Command

If we have numbers in text format, the previously used formula will return a #N/A error as shown in the picture below. So to accomplish the same task as in the pervious example, we’ll have to change the format of the ID numbers present in Column B.

Steps:

Select the range B5:B14 containing the order IDs.
Go to the Data tab and select the Text to Columns command from the Data Tools drop-down.

A wizard box will open.

In the dialog box, select the data type as Delimited.
Click Finish.

The delimiters found with the numbers will be removed and the IDs will be in number format. The previously used formula in cell E17 now works as expected.

Now Autofill the other output cells (E18:E21) like before.

All the expected data is returned, as shown in the screenshot below.

2.2 – Using the TEXT function with VLOOKUP

Another option to look up an order ID in a range of numbers formatted as text is to use the TEXT function to define the lookup_value argument in the VLOOKUP function. The selected order ID number will be converted into text format, and then we’ll use this text formatted lookup value to find its match in Column B.

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, the expected output is returned.

2.3 – Using the VALUE Function with VLOOKUP

Now let’s consider the opposite case, where the lookup value is in text format but the order IDs in the table are in number format. We’ll use the VALUE function to convert the lookup value from text format into a number before performing the lookup.

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, the correct results are returned.

Download Practice Workbook

VLOOKUP with Numbers.xlsx