Alternatives to Vlookup

Last Updated :

-

Blog Author :

Tanuj Kumar

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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.

Table of contents

#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

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.

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

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