# Alternatives to Vlookup

Article byTanuj Kumar
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

VLOOKUP is a useful function to reference in columns, but there are also alternatives because it has limitations. As it can reference from left to right to reference from right to left, we use a combination of the INDEX and MATCH function, which is one of the best alternatives to VLOOKUP in Excel.

For example, suppose you have a list of students’ names (John, Tom, Michael) in the left column and their marks in Maths, Chemistry, and Biology in the right columns from B to D. From the table, you need to know the scores. From the data set, you need to know John’s marks in Maths. First, you will need to put the lookup value of John. Then, Excel will look for the lookup value for John in the leftmost column of the specified array. As the VLOOKUP function identifies John’s name, it will move to the second column of the array and return the value in the same row as that of John. This function will only search for the exact matches.

## Vlookup Alternatives

The in a list and return any column from the lookup column. But, if your lookup column is not the first one, then VLOOKUP will not work. Instead, copy the lookup column to a newly inserted first column. Then, you can apply the lookup and across a row and return a value from a column.

### #1 – LOOKUP Function as VLOOKUP Alternatives

Here, we use LOOKUP and INDEX MATCH as alternatives to VLOOKUP in Excel. The lookup function is better than VLOOKUP because it is less restrictive. It was first introduced in MS 2016.

You can search data both vertically and horizontally. It also allows left-to-right and right-to-left lookup. However, Excel alternatives to VLOOKUP only allow left to right. Here, you may understand the working of the lookup function with some examples.

### VLOOKUP Excel Function – Explained in Video

#### LOOKUP Function with Excel Alternative to VLOOKUP – Example #1

Let us consider the following name, country, and age data and apply the LOOKUP function.

Select the name entered in the F4 cell and apply the lookup formula below. =LOOKUP(F4,A3:A19,C3:C19).

To get the age column,

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#### Right-to-Left Functionality Using LOOKUP – Example #2

The most significant benefit of the lookup function is that it can operate from right to left. You may see the processing of the  in the below example.

If you want to switch the procedure to search for age and output the corresponding name, it may work for LOOKUP but produces an error for alternatives to VLOOKUP.

=LOOKUP(F3,C2:C18,A2:A18)

And get the output of the corresponding name.

### #2 – INDEX / MATCH Function as Vlookup Alternatives

#### Example #3 – Using INDEX MATCH

Let us consider the data below and find the age using the name from the table and the standard INDEX MATCH and VLOOKUP formula.

=INDEX(\$I\$2:\$K\$19,MATCH(M6,\$I\$2:\$I\$19,0),3)

=VLOOKUP(M6,I2:K19,3,0)

Here, you will get the desired output from both formulas as both can search for the age from the data.

#### Example #4 – Right-to-Left Functionality Using INDEX MATCH

Let us consider the data below and find the name using the age from the table using the standard INDEX MATCH and VLOOKUP formula.

=INDEX(R24:T41,MATCH(M27,\$K\$24:\$K\$40,0),1)

=VLOOKUP(M27,I23:K40,3,0)

You can easily see that you will get the name from the table using an INDEX MATCH. But you can get a #N/A error from the VLOOKUP formulas as VLOOKUP cannot lookup values from left-right.

### Things to Remember about the Alternatives to Vlookup in Excel

• Using the INDEX MATCH is better than a simple VLOOKUP function.
• The INDEX MATCH can lookup right to left.
• Insert and delete columns safely.
• No limit to a lookup value’s size using the VLOOKUP function. The length of your lookup criteria should not exceed 255 characters. Otherwise, it will be through #Value .
• Higher processing speed than standard VLOOKUP.

You can download these Alternatives to Vlookup in Excel template here – Alternatives to Vlookup Excel Template.

This article is a guide to Alternatives to VLOOKUP in Excel. We discuss alternatives to VLOOKUP using the LOOKUP function and INDEX MATCH with Excel templates. You may also look at these useful functions in Excel: –