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 VLOOKUP function is used to lookup a valueVlookup Function Is Used To Lookup A ValueThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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 valueIndex Match Combination To Lookup A ValueThe INDEX function can return the result from the row number, and the MATCH function can give us the position of the lookup value in the array. This combination of the INDEX MATCH is beneficial in addressing a fundamental limitation of VLOOKUP.read more 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.

lookup function example 1

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

lookup function example 1-1

To get the age column,

lookup function example 1-2

–>> 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 LOOKUP function in ExcelLookup Function In ExcelThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more in the below example.

lookup function example 2

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)

lookup function example 2-1

And get the output of the corresponding name.

lookup function example 2-2

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

Using Index Match Example 1

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

Using Index Match Example 1-1

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

Using Index Match Example 1-2

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.

Using Index Match Example 1-3

Things to Remember about the Alternatives to Vlookup in Excel

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: –