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
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
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
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).
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
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.
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.
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 :
Note: Stay tuned as we are going to provide you more examples on this to make you understand better.