VLOOKUP functions of excel is very useful when you need to return a value from a table or a range of cells by looking up another value. VLOOKUP on excel can be used for finding the value in a range which are present in the other range in the same sheet or other sheet and other excel files. Let is quickly look into the syntax of vlookup function and examples on how to use VLOOKUP on Excel. Do note than simple VLOOKUP function works with only single criteria. If you are looking for 2 or more criteria vlookup, the please refer my previous post multi criteria vlookup.

**Syntax of Excel VLOOKUP Function**:

**Value**: is the value to search for in the first column of the table_array or range of cells. Value can either a text or a numerical value.

**table_array**: is the range of cells where you need to lookup for the value.

**index_number**: is the column number in table_array from which the matching value must be returned. A

**index_number**argument of 1 returns the value in the first column in table_array; a

**index_number**of 2 returns the value in the second column in table_array, and so on.

**Typeoflookup**: This is not mandatory. However this will chose between exact match and relative match. If you use TRUE or if you omit this argument, then non exact match lookup will be performed. If this is set to FALSE, then vlookup does the exact match

**Note and Troubleshooting Tips on Excel**:

- If “Typeoflookup” is either TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.
- If “Typeoflookup” is FALSE, the values in the first column of table_array do not need to be sorted.
- Excel vlookup returns #VALUE! if “index_number” is less than 1 and returns #REF! if the “index_number”Greater than the number of columns in table_array. Also one more error value comes with VLOOKUP is #N/A. Excel returns #N/A when there is no exact match or the value you are matching in the range is not found.
- If there are two or more values in the first column of table_array that match the lookup_value, the first value found will be returned

**Example on Using VLOOKUP formula in Excel**:

Sample data |

**Using vlookup with Exact match**:

**Using vlookup with non Exact match**:

**Formatting the VLOOKUP on Excel using ISNA and IF functions**:

When there is no match found, excel returns #n/a. This is will give problems when you need to use SUM function on the return value of returned value.To overcome this, we need to use ISERROR function. This very useful, if you are creating a template with VLOOKUP function.

**Examples**:

**Note**: Do not use this function unless you have earlier versions of excel as this will lead to calculation time. This is because for every time you use the function, excel needs to calculate vlookup twice. when range to lookup is more and number of instances of using this formula more, then excel will take really long time to calculate and return the right values.

Please do share your feedback and tips which you know in the comment section below. If you need any specific support on Excel, then mention your query in comment section, I will try to answer your query.

I want all excel formula