Index & Match Combination (Reverse Vlookup) – Excel Formula

0
Reverse Vlookup ( Index & Match) Combination Excel Formula
Reverse Vlookup ( Index & Match) Combination Excel Formula

Here you are going to learn the way how you can use the Index & Match excel formulas combination (Reverse Vlookup) with examples in order to get the Hlookup, Vlookup outputs and more outputs in excel spreadsheet.

Index & Match combination also known as reverse Vlookup is powerful function in excel  which is very powerful than the Vlookup. Because it has lot of features to prepare excel reports. We can find any column header while preparing report using this function. Vlookup will have only one feature like pulling the right side value based on left side selected value. Here Reverse Vlookup or Index & Match combination can perform the right side value based on left side selected value, left side value based on right side selected value. Hlookup and Vlookup functionalities have been included in this combination function by Microsoft team.

Left Side Value Based On Right Side Selected Value

Example 1:


If you have data with Case, Contact Name, Customer Name in the same order. If you need to pull the Case details with Contact Name or Customer Name, you must use the Index & Match function. Please find below the screenshot to understand the data which is there in the “Index & Match Combination” Sheet

Index & Match Combination (Reverse Vlookup) required Data
Index & Match Combination (Reverse Vlookup) required Data

Now in the Result sheet, you have Contact Names to pull the Case details in the next column as shown in the below screenshot and here itself you can see syntax of the formula in “Result” Sheet

Index & Match (Reverse Vlookup) Excel Function Results
Index & Match (Reverse Vlookup) Excel Function Results

Please find below the syntax to understand the formula that we have used to pull the result sheet case information using the contact name (Left side value based on right side selected value).

Index & Match Combination (Reverse Vlookup) Excel Function Syntax
Index & Match Combination (Reverse Vlookup) Excel Function Syntax

If you see the above, Index Formula syntax has Array, Row, Column properties. In order to find the particular row of the contact name, we have used the Match function within the index formula in the place of row property. When you say array, you need to select from Case column to Contact Name column in the “Index & Match Combination” Sheet as you can see above the Syntax Screenshot. Once you select then you need to use the Match formula in the Row property, then column number can be specified. Here in this case column number is 1 (Case). Now let’s understand Match formula. Match formula is very important excel formula which can pull the matched values based on given input. It has Lookupvalue, Array, matchtype (0,1,-1) properties. We have just used to function within the index to pull the left side value based on right side selected value which is impossible by Vlookup function. Once you arranged the formula in one cell, the same can be dragged till end row of the result sheet data. Once you drag, you can see “case” details which are available in the Array property related sheet here in this case “Index & Match Combination” Sheet. Hope you understand the first example of the Index & Match combination or Reverse Vlookup function in Excel Spreadsheet.

Right Side Value Based On Left Side Selected Value

Example 2:


Now you are going to do the Vlookup formula functionality with Reverse Vlookup function. In order to do that let’s use the same above example related data and get the Customer contact values using Case details. Please find below the screenshot to understand more better way.

Index & Match (Reverse Vlookup) Excel Function Results Right side values pulling based Left side value Vlookup functionality
Index & Match (Reverse Vlookup) Excel Function Results Right side values pulling based Left side value Vlookup functionality

Here we can see the function to understand the Vlookup functionality with Index & Match Combination or Reverse Vlookup. Hence this combination of formula is very strong among all excel formulas. Now let’s understand the syntax using below the screenshot.

Syntax for Vlookup functionality with Reverse Vlookup (Index & Match Combination)
Syntax for Vlookup functionality with Reverse Vlookup (Index & Match Combination)

We have just taken the array property of index formula as “Index & Match Combination” Sheet Column A to Column B as those are related Case & Customer Name. And we just reversed the requirement first Customer Name is there, hence we just need to pull the Case details in the next column. In order to do this again we have used the Match function and in that now Lookvalue property would be Case, array property would be “Case” column in the “Index & Match Combination” Sheet that is 1st column, matchtype property would be 0 (zero) as we need exact matched row. Once it is done, then column of the Index formula would be 2. Because Customer Name column is 2nd Column in the data source. Screenshot is available to understand the Syntax and match with this explanation.

Formulas of above both cases are similar but slide differences. As per first example we just took the 2nd column as base in order to get the left to right and as per second example, we just reversed our requirement 1st column as base and 2nd column as result one. First try to understand properties of the Index formula. Then understand properties of Match formula. That’s it you can play around excel spreadsheet with this formula. As of now you just saw only half part of the Reverse Vlookup. Here let’s summarize the properties meaning in Index and Match formula to get in dept knowledge of this combination formula. Array means data set which has all required columns here in our case 1st and 2nd columns of “Index & Match Combination” Sheet those are Case and Customer Name columns. Row means which is the row do we need to get into the result, columns means which is the columns that we need refer to get the value. This is all about properties of Index formula.

Now let’s know about Match formula properties. LookupValue means the value which we need to look, here in our case Result sheet related Case value. Array means again in the “Index & Match Combination” sheet in which column result is available that particular column alone. Matchtype means it is just exact match or greater than match or less than which are indicating with 0, -1, 1 values. Here in our case we have used only 0 (Zero) for exact matched values.

See Also :

Vlokup – Excel Formula
VLOOKUP Error types 
How To Use If And VLOOKUP Together

Note: Stay tuned as we are going to provide you more examples on this to make you understand better.

How To Search Data Values with Vlookup Function in Excel Spreadsheet

0

Here I am going to train you to learn the way how you can search the data values with Vlookup formula in excel spreadsheet. It helps you a lot when you need to search some duplicate values and all in excel. Vlookup is a valuable function in excel spreadsheet. In fact this function has many traits to deal with excel data easily without any support of other excel functions. Hence I thought of providing you the search trick with Vlookup. It is very important when you dealing with important data or sending essential information to client or your management or to any one else in your organization.

How To Modify Vlookup Function Using User Defined Function

0

Here you are going to learn the way how you can modify the Vlookup functionality, when there is a requirement to do in your Excel report using User defined function technique.

In order to do this, you need to add the module in the excel by pressing the Alt+F11 key or selecting the view menu then select the view macro option.

Once you get the module, you need to specify the function name and functionality to modify the Vlookup according to your requirement. This is a very simple process to excel beginners also. Any excel function can be modified using this method.

 

How To Find The Blank Cells Rows And Remove In Excel VBA

0

Here you are going to learn trick about how to find the blank cells in the spreadsheet data and remove them or remove entire row using Excel VBA (Macro) programming with Example program.

In order to find and remove the blank cells from the data in excel spreadsheet, we need to find the last row of the range. Once we find the last row, then we can use loop to check each cell or row in the data using if condition. Once we find the blank row or cells, we can just write a code to remove that particular cell or row. Similar way for all rows we can repeat the loop

 

How To Find The Last Row of Range in Excel VBA ( Macro)

0

Here you are going to learn the way how you can get the last row of the range using various types techniques in Excel VBA programming. Excel vba also known as excel macro programming. Once you find the last row, you can do many things to automate the activity. Hence finding last row in the given excel range is very important concept in Excel VBA. There are many ways you can get last row in the range. 1) Using usedrow 2) using cells 3) using Range in the respective spreadsheet.

  1. Using UsedRow:
  2. Using Cells        :
  3. Using Range      :

 

 

How to use selenium webdrivers in Excel VBA

0

Here I am going to teach you the way how you can use the selenium webdrivers in excel vba in order to scrape the websites information into excel spreadsheet as per user requirement.

How To Create Named Ranges in Excel

0

Creation of named ranges are explained in deep as we have already seen in the vlookup tutorial.

Previously when we were using excel 2003 this named range was used frequently to automate the excel activities using Excel macros. In stead of ranges, we were using the named ranges as those fixed with particular range of cells, rows or columns. In order to create named range, we just need to select the required range of cells that you want to name, then go to cell address bar and give the desired name, once you give press enter tab. That’s it next if you select that range, you can see the given name for that range in the same cell address bar. You can check the below screen shot for your reference.

Named Range
Named Range

This is the simple method which we can use. we have another method for creating the named range. That option is available in the Formulas Menu -> Name Manager. You can click the name manger and select the desired sheet and range of cells in the respective text boxes which will be available in the Name Manger option, then you can give the desired name. You can check the below screen shots for your understanding purpose.

Name Manager option
Name Manager option

Once you get the above name manager option, you can click the new option or edit option(if you want to change any given name) to name the range of cells, rows or columns as per your requirement. Once you click the new or edit option in the name manger, you get the below option box

Naming the range in the Name Manager Option
Naming the range in the Name Manager Option

Hope you understand basic excel option named range. If you have suggestion, please comment in the below comment box to encourage us.

How To Use Goal Seek Option In Excel

0

There is an option called Goal Seek to find the value of principle amount or number of years or rate of interest when tow values available among there 3 then we can find the 3rd value using this option. Usually this option very useful in Banking sector. Also when you situation to calculate the PNR/100.

This is one of excellent option in the excel to easy calculation process for interest related activities.

How To Use Data Validation in Excel

0

Here you are going to learn the way you can do the data validation in Excel. Data validation option can be used to validate the data in excel. Data in the sense there might be numbers or text based on the requirement. This is the one best features in the excel spreadsheet to restrict end users from committing errors by selecting incorrect data. Also this feature can be used to create many options to end user while updating statements or forms. Data validation has many options and varieties. I am going to give you some of examples to explain you in depth for all the options available in this feature.

Data Validation Option in Excel spreadsheet
Data Validation Option in Excel spreadsheet

How To Create Hyperlink in Excel

0

Creating hyperlink is an easy method where you can just go into particular cell and right clink then select the option called hyperlink option or you can just go to insert menu then select the Hyperlink option which is available in the menu bar. Once you select hyperlink option within these 2 options, you have to select location either within the document or outside the document.

Hyperlink using mouse right click:

Hyperlink using mouse right click in Excel
Hyperlink using mouse right click in Excel

 

Hyperlink using Insert Menu:

Hyperlink using Insert Menu
Hyperlink using Insert Menu

Once you select either one of the methods in the above, you will get the below screen to link with other sources.

Hyperlink screen to link with other source
Hyperlink screen to link with other source

In the above screen we need to choose correct option based on our requirement. If you need to link with web page or Place in the current Document options most of the times in the Excel.