How to use Vlookup Formula in Excel
Introduction:- How can we lookup Formula in any data to the data vertically, today we will learn this.
Here a data is visible in front of you, Unique ID is visible in the data that is in A column. And we have to Lookup this data, so we know how to Lookup it.
We have some data in which to get some data on the behalf of ID Number.
If we don't get the data from Vlookup then we have to manually go and find the data.
And we want that we do not have to do it manually, automatic data should come up from the formula.
How to use Vlookup Function
Type 1 :- B28 Cell
=VLOOKUP(A28,A1:M22,2,0)
To pick up a date:- We apply the formula of Volokup on B28, First we will select the Lookup value, Then we will select the data from which we want to pick up the data , then enter the number whose column is to be pickup, here we have to pickup the second column so we have written 2, then write 0 after that for false means for exact match.
To pick up orders value:- We have to do everything in the same way as we did for Pickup the Date, now we have to write 3 in column number so that the third column can be pickup.
In this way, whatever column we want to pickup, we will enter the number of that column.
For each column we have to apply different formula
This is a big problem, I have to apply the formula again and again, it is very boring.
If we want to do it easy we can do it easy
How many types we can use Vlookup
Type 2 :- B28 Cell
=VLOOKUP(A28,A1:M22,2,0)
We have applied this formula, in this we will have to freeze some things so that the formula can be absorbed quickly.
=VLOOKUP($A28,$A$1:$M$22,2,0)
=VLOOKUP($A28,$A$1:$M$22,3,0)
=VLOOKUP($A28,$A$1:$M$22,4,0)
=VLOOKUP($A28,$A$1:$M$22,5,0)
etc.
By putting a $ sign in front of the question, when we drag it to the right side, then the column of the lookup value will not change, If change happens then our answer will be wrong
$A$1:$M$22
freezing the data with $ will not change the data and our answer will be correct
Column number will also have to be changed, it will not change automatically.
Type 3 :- B28 Cell
If we want to do it easy we can do it easy. We did not have to write the column number again and again in the formula, for this we gave the number to the column in row 26,
So that we can easily Lookup the data by giving reference to the column number.
=VLOOKUP(A28,A1:M22,B$26,0)
In this we put a $ sign in the middle of B26 so that when we drag the formula down, Then the column number should not change, otherwise our answer will be wrong.
Type 4 :- B28 Cell
Another way is that we can use the column formula to find the column number.
=VLOOKUP($A28,$A$1:$M$22,Column(B27),0)
Drag this formula to the right side
In column (B27), we have pickup the column number as the base of the heading
Type 5 :- B28 Cell
There is also a way to do this, if our data is also in the same sequence and here where the data to be picked is also in the same sequence, then we can use array with vlookup
{=VLOOKUP($A28,$A$1:$M$22,{2,3,4,5,6,7,8,9,10,11,12,13},0)}
For this, first we will select the data, and put the formula in the first cell and press Ctrl+shift+enter key, and the data will come in one go.
Conclusion:- We have just learned how to use Vlookup formula, we have tried to explain easily, you support us, we will cover all the topics soon
No comments:
Post a Comment