To search for the growth in population in indonesia what is the correct VLOOKUP syntax

The Excel VLOOKUP function searches for a value by matching on the first column of a table and returns the corresponding value from a certain column in the same row.

This tutorial is talking about how to use the VLOOKUP function by explaining the syntax, arguments, return value, function notes and simple examples in details.
Besides, at the end of the tutorial, we list a lot of practical cases of VLOOKUP formulas that are often used, which include Vlookup across different worksheets or workbooks, Vlookup and keep the formatting of the result cell, vlookup return multiple results and other vlookup formulas in common cases.

Syntax

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

Arguments

Lookup_value (required): The value you are searching for. It must be in the first column of the table_array range.

Table_array (required): The table contains two or more columns where the lookup value column and the result value column locating.

Col_index (required): The specific column number (it is an integer) of the table_array, which you will return the matched value from.

• The first column number is 1 for the left-most column of table_array.

Range_lookup (optional): This is a logical value that determines whether this VLOOKUP function will return an exact match or an approximate match:

• If the range_lookup is either TRUE or is omitted, the VLOOKUP function will return an exact match or an approximate match. If an exact match can’t be found, it will match the next smallest value.
  =VLOOKUP(lookup_value, table_array, col_index, TRUE)
  =VLOOKUP(lookup_value, table_array, col_index)

Important: In this circumstance, the values in the first column of the table_array must be sorted in ascending order;
Otherwise, VLOOKUP might not return the correct value.

• If the range_lookup is either FALSE or is 0, the VLOOKUP function will only return an exact match. If an exact match value can’t be found, it will return a #N/A error value. But if there are two or more values in the first column of table_array that match the lookup_value, the vlookup will only use the value of the first found.
  =VLOOKUP(lookup_value, table_array, col_index, FALSE)
  =VLOOKUP(lookup_value, table_array, col_index, 0)

In this circumstance, the values in the first column of the table_array don’t need to be sorted.

Return value

The VLOOKUP function will return the matched value from a table based on the lookup value in the first column.

Function notes

1. The LOOKUP function only looks for value from left to right.
    The lookup value is in the left-most column, and the result value should be in any column to the right of the look up value.
2. There are two matching modes for VLOOKUP function. They are exact match and approximate match.
    The VLOOKUP function will use approximate match by default.
3. It will return the #N/A error value if the look up value can’t be found.

Examples

This section provides examples to show you how to use the VOOKUP function in Excel.

Example 1: Exact match VS approximate match with VLOOKUP function

1.1 Vlookup exact match:

As the below screenshot shown, how to search for an exact match value “Peach” and return its corresponding value from the third column in the table range B3:E8? You can do as follows to achieve it.

1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.
=VLOOKUP(G4,B3:E8,3,FALSE)

1.2 Vlookup approximate match:

As the below screenshot shown, you want to search for an order ID number “10006”, if the number can’t be found, then do the approximate match.

1. Select a blank cell, copy the below formula into it and press the Enter key to get the result.
=VLOOKUP(H4,B3:F8,3,TRUE)

Notes:

• In the above two formulas, G4 and H4 are the references to cells containing the look up values;
• B3:E8 and B3:F8 are the table ranges where the look up value column and the result value column locating;
• 3 is the column number to return the matched value;
• FALSE indicates finding the exact match value in this case;
• TRUE preforms an approximate match look up. As the look up value 10006 haven’t been found in the first column, it will match the next smallest value 10005 and return the result $9.00 from the third column in the table.

Example 2: Vlookup return blank or specific value instead of #N/A

When doing exact match with VLOOKUP function, it will return #N/A error value if the look up value can’t be found. See screenshot:

How to keep the result cell blank or return specific value instead of #N/A when the look up value can’t be found? Here is the solution below.

1. Copy the below formula into the cell you will output the result and press the Enter key:
=IFERROR(VLOOKUP(H4,B3:F8,3,0),"Not found")

Note: In the formula, “not found” is the content you will display in the result cell instead of #N/A error. For keeping the result cell blank, please replace the text “not found” with a space. And you can change it based on your needs.

More Examples

Vlookup formulas - Across workbooks/worksheets

Vlookup values across multiple worksheets
You can apply the vlookup function to return the matching values in a table of a worksheet. However, if you need to vlookup value across multiple worksheets, how can you do? This article provides detailed steps to help you easily solve the problem.
Click to know more...

Vlookup to compare two lists in separated worksheets
Supposing there are two name lists locating in different worksheets “Name-1” and “Name-2”, for comparing these two lists and finding the matching names in Names-1 if they exit in Names-2. The VLOOKUP formula in this article will do you a favor.
Click to know more...

Vlookup to another workbook
Normally, the VLOOKUP function can only be used in the same workbook. If you want to apply the VLOOKUP function in different workbook, please try the formula in this article.
Click to know more...

Vlookup formulas - Keep result formatting

Keep cell formatting when Vlookup
Normally, when applying VLOOKUP formula, you will get the result without any formatting. How to keep the source formatting of the result cell? Here has the solution.
Click to know more...

Vlookup and return background color along with the lookup value
If you want to return the result along with its original background color when applying the VLOKUP formula, method in this article can help.
Click to know more...

Vlookup and return date format instead of number
You may notice that date is displayed as serial number instead of date format when using VLOOKUP function, and then you need to format this result cell to date formatting manually. Except for manually changing the cell formatting to date format, is there any handy way to handle it? This article will show you an easy way to solve this problem.
Click to know more...

Vlookup and return matching value with cell comment
When you apply the Vlookup function to return the matching value, it will return the value only. Sometimes, you may need to vlookup and return the matched value including its original comment. How could you get it down?
Click to know more...

Vlookup formulas - Return multiple results

Vlookup and return matched values in multiple columns
Normally, applying the Vlookup function can only return the matched value from one column. Sometimes, you may need to extract matched values from multiple columns based on the criteria. Here is the solution for you.
Click to know more...

Vlookup to return multiple values in one cell
Normally, when applying the VLOOKUP function, if there are multiple values that match the criteria, you can only get the result of the first one. If you want to return all matched results and display them all in a single cell, how can you achieve?
Click to know more...

Vlookup and return multiple values without duplicates
Sometimes, you may need to vlookup and return multiple matched values into a single cell at once. But, how could you keep the unique values only when duplicate results appear? Here is the solution.
Click to know more...

Vlookup and return entire row of a matched value
Normally, using the vlookup function can only return a result from a certain column in the same row. This article is going to show you how to return the whole row of data based on specific criteria.
Click to know more...

Vlookup formulas - Other common cases

Easily use vlookup and sum in Excel
Combining the vlookup and sum functions helps to search for a certain criteria and sum all values based on this criteria at the same time. Here we provide detailed steps to help you easily vlookup a certain value, and sum all matches in columns or rows in Excel.
Click to know more...

Backwards Vlookup or in reverse order
In general, the VLOOKUP function searches values from left to right in the array table, and it requires the lookup value must stay in the left side of target value. But, sometimes you may know the target value and want to find out the lookup value in reverse. Therefore, you need to vlookup backwards in Excel. There are several ways in this article to deal with this problem easily!
Click to know more...

Vlookup a certain value and only return true or false / yes or no
Normally, when using the Vlookup formula to search for an exactly match value, the #N/A error value will return if the value can’t be found, or return the proper result when matching the value. However, in some cases, we only need to return Yes or No / True or False in order to simply remind ourselves if a value exists.The solution in this article can help.
Click to know more...

Vlookup in two dimensional table
Sometimes, you need to vlookup to return relative value based on both the row and column, that is to say, you need to return the data from two dimensional table. In this case, combining the VLOOKUP and MATCH function can help to solve the problem.
Click to know more...

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

• Designed for 1500 work scenarios, helps you solve 80% Excel problems.
• Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
• Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
• 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.

Comments (1)

No ratings yet. Be the first to rate!