PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO CONVERT VLOOKUP TO INDEX MATCH

INTRODUCTION

VLOOKUP is one of the very old and successful functions present in Excel. Every new learner wants to learn this function. The reason for this fact is the power of this function which helps us to join any number of reports or tables, within the same sheet or different sheets or different workbooks.

Although VLOOKUP has many advantages like the simplicity and the ease of its use, it still has got a few issues.

The biggest issue of the VLOOKUP is that it can only lookup from left to right which means the column to be matched should be the first column of the LOOKUP TABLE. This problem becomes serious when we don’t have the the matching column on the left.

 

For this situation, a better solution available, which helps us to  overcome  this problem is INDEX-MATCH.

 

INDEX and MATCH are the two different functions in Excel which can be combined to create a STRONG LOOKING UP combination which will lookup for any value or column whether the value to be looked up is to the left of the matching column or to the right of the matching column.

In this article we are going to discuss a particular situation where we want to change the function from VLOOKUP to INDEX MATCH and INDEX MATCH to VLOOKUP without putting our data at the risk.

VLOOKUP AND INDEX-MATCH FUNCTIONS

Just for the revision.

CLICK FOR COMPLETE ARTICLE ON VLOOKUP.

CLICK HERE FOR COMPLETE ARTICLE ON INDEX FUNCTION.

CLICK HERE FOR COMPLETE ARTICLE ON MATCH FUNCTION.

CLICK HERE FOR COMPLETE ARTICLE ON INDEX-MATCH USAGE.

 

VLOOKUP FUNCTION LOOKS UP FOR A GIVEN VALUE IN THE FIRST COLUMN OF THE SEARCH TABLE AND RETURN THE VALUE FOR THE REQUESTED COLUMN AGAINST THE MATCHED VALUE.

INDEX FUNCTION RETURNS THE VALUE IN A SEARCH TABLE FOR THE GIVEN INDEX.

MATCH FUNCTION RETURNS THE INDEX OF THE MATCHED VALUE FROM THE GIVEN ARRAY.

 

CONVERSION FROM VLOOKUP TO INDEX MATCH

Before we go to the conversion part, let us take an example which will be done with the use of

VLOOKUP

INDEX MATCH

and then we’d go for the conversion.

EXAMPLE:

Suppose we have two tables, which needs to be combined into the third table using VLOOKUP.

TABLE 1 is given below. [ The table can be copied ]

The table contains the information regarding a bus service.

Bus number is given, Route number is given and number of passengers is given.

TABLE 1
BUS NO. ROUTE NO.NO. OF PASSENGERS
11301026
11321524
11331824
11342328
11352521
11362626
11372720

 

TABLE 1 FOR THE EXAMPLE
TABLE 1 FOR THE EXAMPLE

TABLE 2 :

Table 2 also contains some details of the BUS SERVICE like NUMBER OF BREAKDOWNS and NUMBER OF RUNNING DAYS but the data is not in the structured manner.

We have to find out a single consolidated report for these two tables.

TABLE 2
BREAKDOWNSBUS NO.RUNNING DAYS
2113523
1113624
2113721
3113222
4113322
3113425
2113025
TABLE 2
TABLE 2 FOR THE EXAMPLE

CREATING A THIRD TABLE USING VLOOKUP

Let us join the two tables and create a third and final table.

As we know we need a UNIQUE ID COLUMN [A COLUMN WHICH IS PRESENT IN BOTH THE TABLES TO BE JOINED AND HAVE UNIQUE VALUES ] for successful usage of VLOOKUP. In this example we have the BUS NUMBER which is unique.

Let us extract the information from the second table and join it in the first one.

AS WE DISCUSSED ABOUT A PROBLEM OF THE VLOOKUP, THAT IT CAN ONLY LOOKUP FROM LEFT TO RIGHT. IN THIS CASE [IN TABLE 2] THE ‘BUS COLUMN’ IS NOT THE FIRST ONE BUT WE DO HAVE DATA ON THE LEFT SIDE OF THE UNIQUE COLUMN TOO. SO WE NEED TO COPY THE UNIQUE ID COLUMN AND PASTE IT TEMPORARILY AT THE FIRST POSITION OF THE DATA TO  BE EXTRACTED.

In view of the above statement, we need to create a temporary column or shift the BUS NO. column

The following picture shows the MODIFIED SECOND TABLE from which the data is to be extracted. We have copied the BUS NO. column and pasted it at the position 1.

MODIFY THE TABLE BY COPYING THE COLUMN
COPY THE UNIQUE ID COLUMN [BUS NO.] AND PASTE IT AT THE FIRST POSITION OF THE TABLE

We have created a TEMPORARY COLUMN for our VLOOKUP to work properly and help us to get all the data information from the table.

Now let us put the formulas to get the data.

LOOK AT THE PICTURE BELOW.

FETCHING THE DETAILS FOR BREAKDOWN:

  • Select the cell where we want to fetch the number of BREAKDOWNS. [ I23 for our example].
  • Put the formula as =VLOOKUP( UNIQUE ID CELL, LOOKUP TABLE, COLUMN NUMBER TO RETURN THE VALUE, FALSE FOR EXACT MATCH )
  • For our example, we put the formula as =VLOOKUP(F23,$Q$7:$T$13,2,FALSE) [ The $ which is not visible in the picture below has been placed so that our lookup table doesn’t change while dragging down the formula].
USE VLOOKUP IN EXCEL EXAMPLES
ENTER THE FORMULA AS SHOWN BELOW TO FETCH THE DATA
After the formula is put, the value will be fetched from the table as 2, check if it is correct.
If the value fetched is correct, drag down the formula using the plus handle at the lower right corner of the cell through the column.
The complete column will be filled.

FETCHING THE DETAILS FOR RUNNING DAYS:

  • Select the cell where we want to fetch the number of RUNNING DAYS. [ J23 for our example].
  • Put the formula as =VLOOKUP( UNIQUE ID CELL, LOOKUP TABLE, COLUMN NUMBER TO RETURN THE VALUE, FALSE FOR EXACT MATCH )
  • For our example, we put the formula as =VLOOKUP(F23,$Q$7:$T$13,4,FALSE)
  • LOOK AT THE PICTURE BELOW.
  • The value returned is 25, which is correct.
  • Now drag down the formula using the + sign in the right lower corner of the cell through the column.
VLOOKUP VS INDEX MATCH
ENTER THE FORMULA AS SHOWN BELOW TO FETCH THE DATA FOR RUNNING DAYS COLUMN

The final table created using the VLOOKUP is shown below.

HOW TO CONVERT VLOOKUP INTO INDEX MATCH IN EXCEL
FINAL TABLE CREATED USING VLOOKUP

CREATING A THIRD TABLE USING INDEX MATCH

We have already joined the data of two tables using the VLOOKUP. Now let us perform the same job using the INDEX MATCH.

The two tables remain the same.

STEPS  TO FETCH THE DETAILS FOR BREAKDOWN USING INDEX MATCH:

  • Select the cell where we want to fetch the number of BREAKDOWNS. [ I36 for our example].
  • Put the formula as =INDEX (SEARCH TABLE, MATCH (SEARCH VALUE, MATCHING COLUMN, MATCH TYPE ), RETURN COLUMN NUMBER )
  • For our example, we put the formula as =INDEX($K$7:$M$13,MATCH(F36,$L$7:$L$13,0),1) [ The $ is used for the absolute addressing so that the references doesn’t change while dragging any formula ]
HOW TO USE INDEX MATCH IN EXCEL
ENTER THE INDEX MATCH FORMULA TO FETCH THE DATA FOR BREAKDOWN COLUMN

After putting the formula, press ENTER. The value visible would be 2.

Check if the value is correct. For our example, the value is correct.

Drag down the formula through the column using the DRAG HANDLER in the lower right corner of the cell selector.

FETCHING THE DETAILS FOR RUNNING DAYS:

  • Select the cell where we want to fetch the number of RUNNING DAYS. [ J23 for our example].
  • Put the formula as =INDEX (SEARCH TABLE, MATCH (SEARCH VALUE, MATCHING COLUMN, MATCH TYPE ), RETURN COLUMN NUMBER )
  • For our example, we put the formula as =INDEX($K$7:$M$13,MATCH(F36,$L$7:$L$13,0),3)
  • LOOK AT THE PICTURE BELOW.
  • The value returned is 25, which is correct.
  • Now drag down the formula using the + sign in the right lower corner of the cell through the column.
EXCEL INDEX MATCH
ENTER THE FORMULA AS SHOWN BELOW TO FETCH THE DATA FOR RUNNING DAYS COLUMN USING INDEX MATCH

The final table created using the INDEX MATCH is shown below.

FINAL TABLE USING INDEX MATCH
FINAL TABLE USING INDEX MATCH

CONVERSION FROM VLOOKUP TO INDEX MATCH

We have used both the methods VLOOKUP and INDEX MATCH to fetch the data and created a new table. It was just an example and we can use the same method even if the tables are in different sheets or workbooks. You can refer to the articles mentioned above to learn about them.

Now it is time to generalize what we have learnt and find out the conversion.

Have a look at the picture below.

In the picture below, we are trying to search for the VALUE IN COLUMN E AGAINST THE THE ID 3 using both the VLOOKUP AND INDEX MATCH to get the conversion general formula.

Have a look at the formulas used

 RESULTFORMULA USED
USING VLOOKUP2=VLOOKUP(J11,J9:L12,3,FALSE)
   
   
USING INDEX MATCH2=INDEX(J9:L12,MATCH(J11,J9:J12,0),3)

 

SEARCHING THE SAME VALUE USING VLOOKUP AND INDEX MATCH
The result of both the methods are same.
But as we saw that we don’t need to create any helper column in the INDEX MATCH even if the data to be retrieved is on the right side only.
So, generalizing the formula
IF THE GIVEN VLOOKUP FORMULA IS OF THE FORM
 VLOOKUP ( VALUE TO BE SEARCHED, LOOKUP TABLE, COLUMN TO BE RETURNED, APPROXIMATE OR TRUE MATCH)
The equivalent INDEX MATCH FORMULA will be
INDEX (LOOKUP TABLE, MATCH ( VALUE TO BE SEARCHED, ARRAY IN WHICH VALUE IS TO BE SEARCHED, EXACT OR LESS THAN OR GREATER THAN MATCH) , COLUMN TO BE RETURNED)
 
The only extra entry or parameter in the INDEX MATCH is the COLUMN OF THE LOOKUP VALUE which is by default the first column of the search table in case of the VLOOKUP.
Rest all the parameters are same.
 
The formula can be applied directly.
Further simplifying
If we put the parameter number like 1,2,3,4
If VLOOKUP  formula is
=VLOOKUP (1,2,3,4)
The equivalent INDEX MATCH formula will be
=INDEX(2, MATCH(1, ARRAY OF THE LOOKUP VALUE, 4), 3)
Quite simple! Isn’t it.
 
 

CONVERSION FROM INDEX MATCH TO VLOOKUP

We just learnt how we can convert VLOOKUP FUNCTION into INDEX MATCH FUNCTION.

But what if we want to  convert INDEX MATCH combination into VLOOKUP.

Now it is time to generalize what we have learnt and find out the conversion.

KINDLY REFER TO THE PREVIOUS SECTION FOR THE FORMULAS WHICH WE DERIVED TO FETCH THE VALUES IN A TABLE UTILIZING THE VLOOKUP AND INDEX MATCH.

 

We will directly use the GENERALIZED FORMULA FOR THE CONVERSION OF VLOOKUP TO INDEX MATCH
 
IF THE GIVEN VLOOKUP FORMULA IS OF THE FORM
 VLOOKUP ( VALUE TO BE SEARCHED, LOOKUP TABLE, COLUMN TO BE RETURNED, APPROXIMATE OR TRUE MATCH)
The equivalent INDEX MATCH FORMULA will be
INDEX (LOOKUP TABLE, MATCH ( VALUE TO BE SEARCHED, ARRAY IN WHICH VALUE IS TO BE SEARCHED, EXACT OR LESS THAN OR GREATER THAN MATCH) , COLUMN TO BE RETURNED)
 
The only extra entry or parameter in the INDEX MATCH is the COLUMN OF THE LOOKUP VALUE which is by default the first column of the search table in case of the VLOOKUP.
 
Rest all the parameters are same.
 
The formula can be applied directly.
 
Further simplifying
If we put the parameter number like 1,2,3,4
If VLOOKUP  formula is
=VLOOKUP (1,2,3,4)
The equivalent INDEX MATCH formula will be
=INDEX(2, MATCH(1, ARRAY OF THE LOOKUP VALUE, 4), 3)
We are done!!
 
 

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]