Hi friends,
Today we
learn about how Vlookup can retrieve the value of multiple columns from a single
formula.
Lets learn the matter with the following example :
We have a table above with Name in column A and East, West,
North, South in the row 1 field and
table
has some random data within the table range from A1:E7.
Now to understand the example in a better way we have to draw
a second table with no data, is
mentioned below:
Now we have a blank table and this table has no data and with
the help of given data in Table1 there
is one way we are familiar with i.e.
simply type formula =VLOOKUP(H1,A1:E7,2,FALSE) but
this formula is basic
formula and to look for each record we have to type the formula every single
time, but today we will discuss about a single formula to drag all the cell
result.
So here is another table to understand it in better way.
I have typed the formula above and described it below
1.
Lookup_Value : We are looking the value of H1 in column I1 from
Table1
1.
Table_array
: We will the find the value of this cell from table range $A$1:$E$7 (Table1)
1.
Col_Index_num : Now
this is the main field column, we will look for the value of each column
by
typing the vlookup formula in each
column, but by the help of vlookup columns
formula we do not need to type the vlookup formula each
time, we will
understand it from above mentioned example(Table3).
False : And the last argument is True/False(Approximate/Exactly)
In the above mentioned example you are watching that there
are $ sign in the formula, this $ has a
important role in formula writing in Excel
and we can put this $ by pressing F4 key repeatedly to get
the result accordingly. This $ sign
fixing a row or column i.e. $H1 (H: Column, 1: Row),
$H1 column (H) will be freeze
and will not change but row 1will change whenever we will drag it
right or
down.
So if you will exactly type the above formula in the table
and press Enter, it will retrieve all data from
Table1, all fields will get
their data automatically.
I will give a separate
post for Absolute Cell
Reference later
Now focus on third arguments i.e. Col_index_num COLUMNS($B1:B1)+1
Here is the detailed description $B1:B1 represents the same
column but when you drag it first $B
will be remain same and second B will be change to C,
D, E, F and so on and after closing the
bracket type +1 and it will automatically look for the
next column, you can see by deleting the +1
from the formula and see then what
happened.
So friends , soon I will be back with another useful post
goodbye for now
Narendra
