Alternatives to Vlookup

Published on :

21 Aug, 2024

Blog Author :

Edited by :

Reviewed by :

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 VLOOKUP function is used to lookup a value 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 INDEX MATCH combination to lookup a value across a row and return a value from a column.

Vlookup Alternatives

#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,

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 LOOKUP function in Excel 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 match as Vlookup alternatives example 1

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

Index match as Vlookup alternatives example 1-1

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

Index match as Vlookup alternatives example 1-2

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

Index match as Vlookup alternatives example 1-3

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 error in Excel.
Higher processing speed than standard VLOOKUP.

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