How to Use the VLOOKUP Ascending Order in Excel (3 Ways)

result

The VLOOKUP function is a game-changer when you have to retrieve information from a range of data in the same or different worksheets. This function is one of the most popular Excel functions. You can look up data, return them in a new worksheet and sort them in ascending order. Today in this article, we will discuss three different methods to Use the VLOOKUP Ascending Order in Excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

VLOOKUP in Excel: Definition

The “VLOOKUPor “Vertical Lookup” function takes the input value, searches it in the worksheets, and returns the value matching the input. This function is available in all versions of Excel from Excel 2007.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Argument

  • Lookup_value: The value we want to find by searching it in another worksheet (Required)
  • table_array: The range of cells in another worksheet containing out input data (Required)
  • Col_index_num: The specific column number in the sheet_range containing the information we want to achieve (Required)
  • [range_lookup]: value is either TRUE or FALSE (optional)
    • if TRUE, returns either an exact or approximate match.
    • if FALSE, VLOOKUP will find an exact match.

VLOOKUP in Ascending Order: 3 Ways

We can return our values from the data table in the same or different worksheets and sort them in ascending order in a new worksheet. We will discuss three distinctive ways to do it.

1. VLOOKUP in Ascending Order in the Same Worksheet

To learn this method, let’s follow the following steps.

Step-1:

The following example includes a range of data where “ID”, “Salary”, and “Region” of some Sales Rep are given. We need to sort them in ascending order using the VLOOKUP function.

creating table

Step-2:

Make another data table anywhere in the same worksheet where you want to retrieve the data in ascending order. Insert the ID numbers in the “ID” column serially.

creating table

Step-3:

Apply the VLOOKUP function in the “Salary” column.

The final form of this formula is

=VLOOKUP(F4,$B$4:$D$14,2,FALSE)

Where,

  • Lookup_value is the highest number
  • Table_array is $B$4:$D$14.
  • Col_index_num is
  • [range_lookup]: we want the exact match (FALSE).

applying the function

Press “Enter” to get the result. Select the rest of the cells and apply the same function.

getting the result

Step-4:

Now we want to get the value for the “Region” column too in ascending order. To get this result change the absolute cell references from “$B$4:$D$14” to “B$4:D$14”.

result

Our result is here. Click and drag the mouse to get the same result in the remaining cells.

the final result

2. VLOOKUP in Ascending Order from Another Worksheet in the Same Workbook

Step-1:

Create a data table in another worksheet where you want to return data in ascending order according to your lookup value.

creating table

Step-2:
Apply the “VLOOKUP” function in the new worksheet.

The final formula is

=VLOOKUP(B4,’M1′!$B$4:$D$14,2,FALSE)

Where,

  • Lookup_value is the highest number
  • Table_array is ‘M1’!$B$4:$D$14. Click on the M1 sheet and select the
  • Col_index_num is
  • [range_lookup]: we want the exact match (FALSE).

applying formula

Press “Enter”. Select the column to get the result for all the cells.

result

Step-3:

To get all the values in both “Salary” and “Region” columns, change the blocked cell reference from “M1′!$B$4:$D$14” to “M1′!B$4:D$14”. Press “Enter”.

overall result

Click and drag the mouse to get the rest of the result.

final result

3. VLOOKUP in Ascending Order from Multiple Worksheets Using IFERROR

We can look up our data in multiple worksheets and then sort them in ascending order by using the “VLOOKUP” with the “IFERROR” formula. This formula will look up for value in every worksheet until it finds the exact value that we are looking for.

Step-1:
Create two data worksheets in which we will look up for value. The data for “UK” worksheet.

data table

And the data for the “USA” worksheet.

data table

Step-2:

Make a new data table in a new worksheet where we want to sort our data in ascending order.

creating table

Step-3:
Now, Apply the “VLOOKUP” and the “IFERROR” function combinedly.

The format for this formula is,

=IFERROR(VLOOKUP(MAX(..)…), IFERROR(VLOOKUP(MAX(..)…), …, “Not found”))

Now insert out data in this formula. The final look of the formula is,

=IFERROR(VLOOKUP(C4,’UK ‘!C$3:E$7,2,FALSE),IFERROR(VLOOKUP(C4,USA!C$2:E$7,2,FALSE),”NOT FOUND”))

Where,

  • Lookup_value is
  • table_array is ‘UK ‘!C$3:E$7, USA!C$2:E$7. “Block” the Rows to get the correct result for both the columns.
  • Col_index_num is 2
  • [range_lookup]: we want the exact match (FALSE)
  • If the value is not available, then type “NOT FOUND”.

applying the vlookup formula

Press “Enter”.

getting the result

We have got our values in ascending order. Now apply this formula to the rest of the cells.

final result

The problem in the VLOOKUP Ascending Order

Using the “VLOOKUP” function in ascending order is comparatively easy. However, you might face problems while ascending in order using this function if you don’t use the fourth argument of this function. Let’s discuss.

VLOOKUP Ascending Order Without the Exact Match

There is an optional argument in the “VLOOKUP” function that plays a vital role when we sort in ascending order. That argument is the 4th argument (range_lookup). Let’s see how this argument works.

Step-1:

In the following example, we will find the Salary from the ID in ascending order.

VLOOKUP Ascending Order Without the Exact Match

Step-2:

Apply the “VLOOKUP” function in cell “G5”.

Here,

  • Lookup_value is the highest number
  • Table_array is $B$4:$D$15.
  • Col_index_num is 2.

We will ignore the “range_lookup”.The “VLOOKUP” function will then automatically select the range_lookup as “Approximate match (TRUE)”.

VLOOKUP Ascending Order Without the Exact Match

Press “Enter” to get the result.

Step-3:

From the result, we can see that we did not get the result in ascending order. Because, when the 4th argument is TRUE or omitted, you will get an unexpected result. And then the function will look for an approximate match which you may not want for the lookup value in ascending order. Therefore we get this unexpected result. From the picture, we can see we got Salary “8500” for the ID “113-116” and “4000” for ID “118-120”.

VLOOKUP Ascending Order Without the Exact Match

Solution for VLOOKUP Ascending Order Without the Exact Match

We can solve this problem by using these steps.

Step-1:

Edit the applied VLOOKUP function. In the function argument, we see the 4th argument. Now we will use this.

VLOOKUP Ascending Order Without the Exact Match

Select “FALSE” as your 4th argument to get the “EXACT” match for your lookup value. This time we will retrieve our values in ascending order.

VLOOKUP Ascending Order Without the Exact Match

Press “Enter”. The problem is solved and we got our values in ascending order.

VLOOKUP Ascending Order Without the Exact Match

Things to Remember

⏩The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function “Never” searches for the data on the left.

⏩If there is a multiple highest value in a worksheet, the “VLOOKUP” will only return the first match in the list.

⏩Always use the 4th argument as “FALSE” to get the exact result.

⏩The “VLOOKUP” function is not “case-sensitive”. It does not process upper and lower case text differently.

Conclusion

Lookup for data in ascending order using the VLOOKUP function is discussed in this article.  Though this function is difficult for the new users to comprehend, we tried to make it as simple as possible. Hope this article is useful to you when you are facing problems. You are welcome to share your thoughts if you have any confusion.

 

 

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo