V Lookup is also called Vector Lookup. A vector is a series of data that only occupies only one column or row. LOOKUP will look through this Row or Column to find a specific Value. For Example, Column B of Spreadsheet contain figure, and the adjacent Column C contain Text. LOOKUP will search for the requested figure of column B and return the Result of text contain in Column C.
In Example worksheet below, Column B Contains Salary Details of particular company mentioned & In Column C Corresponding Company name. in this case, LOOKUP value will find which company is appropriate under salary figure you desire.
So, here, Lookup_Value is the cell where the salary is entered (B16), Lookup_vector is the salary Column (A2:A13) & result_Vector is company Name column (B2:B13). So, the formula is:
=LOOKUP(B16, A2:A13, B2:B13). See the Image below for reference,
The result is the nearest Value of Salary Mentioned in B16. I.e. INFOSYS.
VLOOKUP cannot be used on a vector (i.e. On a single, row or Column), but on an array (Multiple Columns or Rows). VLOOKUP searches for the value Vertically down the first Columns of an array.
In case of VLOOKUP, the data we want on current sheet is to be found in another sheet with a single Common data on each sheet. For example, a spreadsheet contains salary sheet of a company, you need to prepare data only with employee name & salary in Hand of each employee.
According to formula syntax, first thing is VALUE – which is common in both sheets i.e. Employee Name, shown in picture below:
Second, after selecting value, select Table you want to grab data from i.e. from another sheet where the common data lies.
Third, Col_index refers to column number, i.e. salary is in second column, select 2 in this reference.
Fourth, range_lookup, select 1 if you want to appropriate Match (True) or 0 for Exact Match (False).
Then final result, will be salary of Referred cell.
One of the difficult tasks to perform & one of the important tools of Excel.
We have 2 different types of data in 2 separate sheets. We need to collaborate this data into a new sheet (Sheet 3). One sheet contains Employee name & Laptop Brands & second sheet Contains Laptop details with their Respective Prices. I want a separate sheet, where in front of Employee Name prices of laptop to be mentioned. So, we used double VLOOKUP Formula
Sheet 1 Sheet 2 Sheet 3
Without any hassle we will use Double VLOOKUP Function, but do concentrate.
Step 1: Use V LOOKUP formula where employee name is common & use it the way, we have studied earlier.
In step above, if you will press enter it will provide you Laptop Brand Name only.
In step 2, In current sheet, we need to refer to sheet 2 to get price of Laptops. So, again write VLOOKUP after equal to sign as mentioned below:
Step 3: after bracket ends, refer to sheet 2, & use same Technique to get your values as shown below:
Result will be displayed:
I hope you had learnt how to use V Lookup, If you liked it, let us know.
If any Query, you can Contact us or Login to get more updates…!!
For Count and SUM Formula please click on below mentioned links