VLOOKUP - How to use VLOOK UP function in Excel or Symphony

The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.

Excel's VLOOKUP function, which stands for vertical lookup, can be used to look up specific information located in a table of data or database.

VLOOKUP normally returns a single field of data as its output. How it does this is:

1.You provide a name or Lookup _value that tells VLOOKUP in which row or record of the data table to look for the desired information

2.You supply the column number - known as the Col_index_num - of the data you seek

3.The function looks for the Lookup _value in the first column of the data table

4.VLOOKUP then locates and returns the information you seek from another field of the same record using the supplied column number

The syntax for the VLOOKUP function is:

= VLOOKUP (lookup_value, Table_array, Col_index_num, Range_lookup) ----> in Excel

= VLOOKUP (Search Criteria, array, index, Sort Order) ----> Symphony

Lookup _value / Search Criteria - (required) the value you want to find in the first column of the Table_array argument.

Table_array / Array - (required) this is the table of data that VLOOKUP searches to find the information you are after

- the Table_array must contain at least two columns of data;

- the first column normally contains the Lookup_value.

Col_index_num / Index - (required) the column number of the value you want found

- the numbering begins with the Lookup_value column as column 1;

- if Col_index_num is set to a number greater than the number of columns selected in the Range_lookup argument a #REF! error is returned by the function.

Range_lookup / Sort order - (optional) indicates whether or not the range is sorted in ascending order

- the data in the first column is used as the sort key

- a Boolean value - TRUE or FALSE are the only acceptable values

- if omitted, the value is set to TRUE by default

- if set to TRUE or omitted and an exact match for the Lookup _value is not found, the nearest match that is smaller in size or value is used as the search_key

- if set to TRUE or omitted and the first column of the range is not sorted in ascending order, an incorrect result might occur

- if set to FALSE, VLOOKUP only accepts an exact match for the Lookup _value.

Sorting the Data First:

Although not always required, it is a usually best to first sort the range of data that VLOOKUP is searching in ascending order using the first column of the range for the sort key.

If the data is not sorted, VLOOKUP might return an incorrect result.

Exact vs. Approximate Matches:

VLOOKUP can be set so that it returns only information that exactly matches the Lookup _value or it can be set to return approximate matches

Model VLOOKUP EXERCISE:

Step 1: Just get the both excel sheets (Main data sheet and required data sheet) in one workbook. (For easy undersanding, Main data sheet is named as "Source", Required data sheet is named as "Target").

 Step 2: Go to Target sheet and click on "fx" button as shown in below.

Step 3: Then select "vlookup" from drop down menu in function tab. Now you will get menu as below

Step 4: First give the coloumn number (like A2, A3, A4) what you want to search on source sheet.

Step 5: Fill the array by selecting the portion of source data where you want to lookup the data in source sheet like source A2:D13

Step 6: Give the coloumn number of source sheet which data you want to reflect in target and

Fill Sort Order as 0 to get exact match value

Now you will get the value in coloumn 3 which is matched with your search criteria.

 

To get Mobile no value for your matching search criteria, Please change Index as "2" (Because Mobile No coloumn is second coloumn in source sheet)

To get Date value for your matching search criteria, Please change Index as "2" (Because Date coloumn is Fourth coloumn in source sheet)

 

Successfully you got all the values matched with your search criteria from source file target file with simple formula

Just try it and post your experiences or queries in comments below