We imported a dataset from Google Sheets to Excel using Coupler.io, a solution for automatic data exports from multiple apps and sources.
How to use vlookup in excel to find matches how to#
The data will look like this.How to vlookup another Excel Online file How to vlookup another workbook in Excel Suppose we have a list of medicines with how much each medicine sold in each month. Where we can change both column and row in the same formula. What if we make Col_Index_Num dynamic? we will have a dynamic two-way vlookup. The third argument Col_Index_Num is always hard-coded. So far we have learned, the vlookup takes 4 arguments. Now that we know about Match Function, let’s start Two Way Vlookup. 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.
![how to use vlookup in excel to find matches how to use vlookup in excel to find matches](https://i0.wp.com/basicexceltutorial.com/wp-content/uploads/2018/05/1-3.png)
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. 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?
![how to use vlookup in excel to find matches how to use vlookup in excel to find matches](https://www.exceltip.com/wp-content/uploads/2013/07/image-377.png)
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.
![how to use vlookup in excel to find matches how to use vlookup in excel to find matches](https://www.excel-easy.com/examples/images/vlookup/first-match.png)
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.