//
archives

Excel Macro

This category contains 1 post

HLOOKUP AND VLOOKUP


clip_image001HLOOKUP AND VLOOKUP

Excel is vast, so does its functionalities. ”Name it and you have it” said an unknown technocrat when few of his fresh bees questioned him “why excel”?

Today we are going to discuss one of the very useful functionality of EXCEL which makes analysis of huge data go like a cake walk.

Let’s assume we have data of a logistics company having around 1000 of records including the pin code, state, city and the courier services used in those cities. One fine day my manager comes and asks me,”hey buddy, I have a list of pin codes, and can you please let me know how many courier companies deliver to code 411001? And yeah please be quick I am on a call with client he wants to know ASAP…”

Now out of this ocean of data how can you search for how many courier companies deliver to code 411001 that too within few seconds?

This is where concept of VLOOKUP AND HLOOKUP comes up:

HLOOKUP: when values which need to be compared are located in a row across the top of a table of data, and what we want to look is a specified number of rows.

VLOOKUP: when values need to be compared are located in a column to the left of the data you want to find.

VLOOKUP /Vertical Lookup/Row Wise Lookup

Let’s consider the same example which I have mentioned above regarding the logistics company and the manager requirements.

This has my sheet which has all data of the logistics company and my manager sheet which is having pin codes against which we need to find certain data.

MYSHEET:

clip_image003

Manager sheet:

clip_image005

Task: No.of courier services against pin code 410403?

Let’s get it!!!!!!!!!!!!!

Firstly, how does lookup like?

clip_image007

  • Lookup value: What value do you want to look up? Here the pin code asked by the manager is present in A6 cell of the manager sheet.
  • Table array: Where is the lookup table? I want it from my sheet.
  • col_index_num: Which column has the value you want returned? Where the courier’s is services column, in my sheet it is the 4th column.
  • [Range lookup]: Do you want an exact match? Is an approximate match okay?

0 means exact match / 1 means approximate match.

Secondly, Steps to use VLOOKUP

  • Select the manager sheet
  • Enter pin code 410403 in manager table or select the pin code if already present in cell A6
  • In cell B6, start the VLOOKUP formula:
      =VLOOKUP(
  • Click on cell A6 to enter its reference.
  • Type a comma, to end the first argument
  • Select the my sheet
  • Select cells A1:d147 (147 rows are there in my sheet thus selecting the entire data)
  • Press the F4 key, to change the cell references to absolute. The formula should now look like this:
    =VLOOKUP(A6,MYSHEET!$A$1:$D$147,
  • Type a comma to end the second argument.
  • Type a 4, which is the column in the lookup table that contains the courier services
  • Type a comma to end the third argument.
  • Type 0, to specify that an exact match for the product code is found, and add the closing bracket.
  • The formula should now look like this:
       =VLOOKUP(A6,MYSHEET!$A$1:$D$147,4,0)
  • Press the Enter key to complete the formula. The courier services provided to the pin code 4101013 will be displayed.
  1. clip_image009

“BE QUICK AND SMART AND WITHIN FEW SECONDS LET YOUR MANAGER/CLIENT KNOW WHAT HE WANTS”!!!

Similarly, HLOOKUP/HORIZONTAL/COLUMNWISE

What we did till now was searching data vertically what if we want to search with respect to the columns.

Let’s have a look …

Now if we have to find that 410103 is the pin code for which state, let’s get it by hlookup

SYNTAX:

=HLOOKUP (“State”, MYSHEET! A1:C147, 3, 0)

When we talk about syntax, same as lookup with a slight difference, here

First argument: what you want to look for however column wise

Second argument: where are you looking?

Third argument: select the data

Fourth argument: which column has the data you want to return?

Last argument: you want exact match or approximate?

Note: Follow same steps as Vlookup to create the formula

Here it will return the o/p as follows

clip_image011

Bhavna

Happy learning

Advertisements
%d bloggers like this: