Menu

*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.*

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.

IT IS ASSUMED THAT YOU HAVE READ AND UNDERSTOOD THE ARTICLES ON VLOOKUP, INDEX , MATCH AND INDEX AND MATCH. IF NOT, KINDLY REFER TO THE ARTICLES MENTIONED ABOVE.

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 |

1130 | 10 | 26 |

1132 | 15 | 24 |

1133 | 18 | 24 |

1134 | 23 | 28 |

1135 | 25 | 21 |

1136 | 26 | 26 |

1137 | 27 | 20 |

**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 | ||

BREAKDOWNS | BUS NO. | RUNNING DAYS |

2 | 1135 | 23 |

1 | 1136 | 24 |

2 | 1137 | 21 |

3 | 1132 | 22 |

4 | 1133 | 22 |

3 | 1134 | 25 |

2 | 1130 | 25 |

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.

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].

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.

**The final table created using the VLOOKUP is shown below.**

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 ]

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.

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

NOW THAT WE HAVE LEARNT BOTH WAYS OF LOOKING UP AND FETCHING THE DATA USING THE VLOOKUP AND INDEX MATCH, WE CAN EASILY CONVERT THE ONE FROM THE OTHER.

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

RESULT | FORMULA USED | |

USING VLOOKUP | 2 | =VLOOKUP(J11,J9:L12,3,FALSE) |

USING INDEX MATCH | 2 | =INDEX(J9:L12,MATCH(J11,J9:J12,0),3) |

** **

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

The equivalent INDEX MATCH FORMULA will be

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

The equivalent INDEX MATCH formula will be

Quite simple! Isn’t it.

IT IS ALSO QUITE EASY TO CONVERT THE INDEX MATCH TO VLOOKUP IF IT SUITS TO THE USER

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

The equivalent INDEX MATCH FORMULA will be

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

The equivalent INDEX MATCH formula will be

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

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.