hello and welcome to our new video in this video we are
going to learn about hlookup function hlookup function
sounds similar to vlookup function in vlookup function we
saw that in vlookup function we put 4 arguments 1st
argument was lookup value 2nd argument was table array 3rd
argument was column number 4th argument was value which was
true or false similarly in hlookup function there is a
slight difference where in vertical lookup in vlookup
function we put column number here in 3rd argument
of column number we put horizontal lookup so here we put
row number this is the difference everything else is same
let me show you how to use this function let me open excel
and here I have some data what is this data? here some
names are given and here 6 month date 6 month sale date
here it is written
Rahul Sale in January
Sale in February
Sale in March
Sale in February
Sale in March
Sale in April
Sale in April all these things are available if I had more
data then how I would find now how hlookup helps us
hlookup's job is from specific row to get data specific row
means suppose this is 6th row from 6th row I want
April's data so it will bring April's data now how it works
let's see what we used to do in vlookup now we are champ if
we have to use any formula or function so here first we
will put equals sign now if I want to use hlookup then I
will write hl will show you double click now see lookup
value second argument table array row index number column
index number because it is vertical and horizontal so here
range lookup we used to put true or false value so here
lookup value now from which value I
to find suppose I want to find
February's data so I am finding from February
I want
Rahul's data
Rahul's February's data so here what we used to do we used
to put name in vlookup but here we will not put name here
we will put
March's data so here in lookup value
I will put March's data after that
I put here
March's data after that second thing
I have to give table array for table array
I have to select whole table and this thing you can see in
formula bar from A1 to H11 we have selected after that I
have to put comma and here it is asking row index number ok
row index number is here in third argument and I have to
put here whose data I want now I know that I have to find
March's data but whose data I want
Rahul's, Hrithik's, Shivam's whose data I want so I said
I want Deepak's data so I will put Deepak's data now I have
to see in which row Deepak is in now how row excel takes
row according to itself one thing is our data is serial
number here 1, 2, 3, 4, 5 6, 7, 8, 9, 10 so you don't have
to take row's number you have to take row's number
according to excel now if I say
I have to find Deepak's data
March's Deepak's data is 21200 this should come in answer
but how it will come now see here if I see serial number
wise it is 5th
I should put 5th here but no according to excel row's
number is 1, 2, 3, 4, 5, 6 in 6th row serial number is 5 so
I will put 6th always remember this here most of the
students make mistake so we don't have to make this mistake
I put 6th here after that I have to put value of false or
true so I know that if things are not written in order so I
write false so here I will write 0 or you can write false
then I closed bracket and here I did enter
I got 21200 is it correct we found Deepak's March's data
21200 this is correct data but here same problem
I have to go here and change things if I want
I don't want March's data
I want to see June's data so for June's data
I have to go here and write June then I did enter let's see
June's data 25490 is it my answer
I did enter 25490 it is correct now let's make it simple
I have to go formula bar again and again so that is
difficult we saw this in VLOOKUP we saw step by step we saw
easy one then we saw one more step that was more easy we
saw one more step that was more easy so here we have to
select any cell and what you have to do you have to write
June's cell's number so that you can see data so let's see
this cell's number this is E cell and this is 14th row so
what I have to do
I have to do
I will remove June's data
and I will write here
E14 so see this it is highlighted and after writing E14
I have to enter you will say invalid value we have seen
this before this is not invalid value because we have not
written data that's why it is showing as I know this is 6th
row now in this 6th row
I have to find
January, February,
March or April because I have birthday in April that's why
April is my favorite month so I have to find April so what
I have to do
I have to enter
April's data here April's data 27,890 so I have entered
27,890 27,890 is it clear? this is easy for us because now
I don't have to change things in formula bar now if I want
May's data so I will write May and I have entered data is
changed 27,560 is it clear? ok now we have seen how data
brings us from specific row now I will insert some rows and
then
I will tell you why I am inserting rows and what is the
benefit remember this here I will insert 3-4 rows then I
will tell you we have seen insert what we have to do right
click and insert now tell me if I want to find date of
March ok let's go back to formula and here I will change
what I have to change
I have to change row's number
I have to know row's number now from where I will count if
we will follow excel 1,2,3,4 5,6,7,8 9,10,11,12 I have to
put 12 and after putting 12 what is May's data 19 1700
let's see after putting 12 it shows something different it
shows something wrong now why it happened you have to
remember that your data starts from here it starts from 6th
row and there is no data in this upper row so when such a
situation comes that your data starts from 4th or 5th or
6th row and the rows given above it are
rows so from where you have to count the rows? you have to
count from here you have to count 1st row, 2nd row, 3rd
row, 4th row, 5th row, 6th row, 7th row i.e.
if I put 7th there then I should get this data let's put it
here
I will put 7
I put 7 here and what should I get in 7? what should I get
in 7? it should be 20600 so let's enter it wait a second
this is wrong slash is gone ok bro ok it was a mistake
let's remove it now let's enter so what is this? 20600 I
think it will be clear to you let's do it again i.e.
I have to find Neeraj's data
I have to find Neeraj's
I have to find April's data so let's put April here it is
showing April's data so I have to change it so what I have
to change?
I have to change row's number now I have to see for Neeraj
what will be row's number? so I will count from here 1st
row, 2nd row, 3rd row, 4th row, 5th row, 6th row, 7th, 8th,
9th
what is in 9th row? it is Neeraj's and I have to find
Neeraj's April's data so what should be the answer? it
should be 11450 so I wrote 9 here and after writing 9, what
I did?
I entered it came to me 11450 is it clear? ok so HLOOKUP is
clear to us that how it works and in HLOOKUP we have seen
that if I have rows if it is blank then what I have to do
in that situation?
I think all things will be clear to you if you still have
any doubt then watch this video again and keep practicing
because practice is very important if you don't practice,
you won't know