This way we can change the column inside the formula without changing the formula.
We will provide the Match function the address of a lookup value just like we do with vlookup. We can use the Match function in vlookup where vlookup asks for col_index_num. Lookup value, Lookup range, and Match type (Exact Match, or Approximate Match) then returns the position of that lookup value. Let us see a new example to learn more.īefore we start talking about Two Way Vlookup, we much know about Match Function in Excel. If we do not give a Lookup_Range argument, by default it takes it as 1. Lookup_Range is responsible for how the vlookup will search the data.
#HOW TO USE VLOOKUP IN EXCEL TO FIND MATCHING DATA HOW TO#
Let us learn how to do it.Īs we know the Vlookup function has four arguments. Vlookup can also be used to find approximate value froom a table. This example was for the Exact match, now is the time to learn the approximate match formula. The only difference is the column number. Amount =VLOOKUP(Account_Number,Data_Range,2,0) Account Type =VLOOKUP(Account_Number,Data_Range,3,0) Branch =VLOOKUP(Account_Number,Data_Range,4,0) Customer =VLOOKUP(Account_Number,Data_Range,5,0) ConclusionĪs you can see the entire formula is the same. Let us see how we will write the formula to show Amount, Account Type, Branch, and Customer. Now the Account_Number and Data_Range will never be changed, it does not matter how much we drag the formula. Now we will use these names in our formula and the formula will be like this. We only need to change the column number and the formula will start showing the data from the desired column. In the above formula, the first argument K1 and range A:E are fixed and will never be changed if dragged. With the fixed arguments the formula will be like this. If we need to fix the K1 so we need to write it as $K$1. We use the dollar sign ($) to fix the argument. We need a way to fix the arguments so that they don’t change upon dragging. For instance, K1 becomes K2 or K3, and so on, depends how far we drag the formula. To save time we write the formula once and drag it.ĭragging the formula will change references. Let’s say three of four vlookup arguments are the same and the only difference is the column number. Many times we write a similar formula multiple times. How to Fix References in Vlookup? Why do We Fix References? 0 represents that user wants Exact Match not Approximate.2 represents the column number in A:E range.A:E is the range of data where the vlookup will look for the desired values.K1 is the address of cell where Account number is saved.
We will write the vlookup formula as shown underneath.
We will use the Vlookup formula in all of these cells. We want to type the account number and want all the below fields self-populated just like Amount in the below screenshot. In the above screenshot, you can see the column names on the right-hand side. We will learn both ways in this tutorial today but let us start with the formula. There are two ways of implementing Vlookup. If you don’t want to remember, Microsoft Excel shows you the formula when typing. It is better if you can remember the syntax. Now you know about the syntax and its components. The false means Exact match and true means Approximate match. This option tells the Vlookup function if you want an exact match or approximate value. For example, the column number of the amount is 2 in the above screenshot. Col_Index_NumĬolumn number of the desired field in table_array. In the above screenshot, from Column A till Column E. This is the data range where you look for the desired record. You find the records based on this field. VLookup Syntax =VLOOKUP(lookup_value,table_array,col_index_num,lookup_range) Lookup_Value The idea is to just enter the account number and all the other fields Amount, Account Type, Branch, and Customer should be populated automatically by vlookup. On the right-hand side of the screenshot, you can see the column names. Imagine how difficult it would be to find out one record from 1 million records. The data has only 18 records but these records can be thousands and even millions. In the above screenshot, you can see the data of bank customers.
0 kommentar(er)
