USE OF V LOOKUP IN ADVANCE EXCEL


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.

V Lookup
Picture 1

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,

V LOOKUP
Picture 2

The result is the nearest Value of Salary Mentioned in B16. I.e. INFOSYS.

V LOOKUP
Picture 3

VLOOKUP

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.

V LOOKUP
Picture 4

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:

V Lookup
Picture 5

Second, after selecting value, select Table you want to grab data from i.e. from another sheet where the common data lies. 

V LOOKUP

Third, Col_index refers to column number, i.e. salary is in second column, select 2 in this reference.

V LOOKUP

Fourth, range_lookup, select 1 if you want to appropriate Match (True) or 0 for Exact Match (False).

V LOOKUP

Then final result, will be salary of Referred cell.

Vlook up
V Look up

DOUBLE VLOOKUP

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

V Lookup

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.

V Lookup
Step 1

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:

V Lookup
Step 2

Step 3: after bracket ends, refer to sheet 2, & use same Technique to get your values as shown below:

V Lookup
Step 3

Result will be displayed:

V Lookup
Step 4

Thank You
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
https://cmaindiagroup.com/how-to-use-count-formula-in-advance-excel/
https://cmaindiagroup.com/sum-formula-advance-excel/

Leave a Comment

//chooxaur.com/4/3629851