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.

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

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 error in ExcelError In ExcelErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more.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: –

  • Vlookup to the LeftStatement of VLookup with IFVLookup in Excel VBAMultiple Criteria of VLOOKUP