EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Lookup & Reference Functions in Excel Excel Alternatives to VLOOKUP
 

Excel Alternatives to VLOOKUP

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel Alternatives to VLOOKUP

Excel Alternatives to VLOOKUP (Table of Contents)

 

 

  • Alternatives to VLOOKUP
  • Examples of INDEX and MATCH Function with VLOOKUP Limitation
  • How to Use VLOOKUP Alternatives in Excel?

Alternatives to VLOOKUP

We have Index Match alternate option to Vlookup, which works far better than Vlookup for returning the value. Although Index Match’s syntax is a little complex. Index Match is the combination of two function Index functions used to look up the value not only from a column but also from the entire table, and Match function is used to carry and fix the position number from where we want to look up the value. Select the Array in Index function and then, with match function, fix the position of the column from where we need to map the data.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Examples of INDEX and MATCH Function with VLOOKUP Limitation

These two are the alternatives to VLOOKUP in excel. For Alternative to VLOOKUP, we need to remember the column number in case of many columns, but in these formulas, we need not remember anything; we just need to understand the logic of the formula.

INDEX Formula:

INDEX Formula

  • Array: What is the array value you want to fetch?
  • Row Num: From which row number you are trying to fetch the data.
  • Column Num: From which column number you are trying to fetch the data.

Index Function – Example #1

Let us look at some examples of Excel Alternatives to VLOOKUP.

You can download this Alternatives to VLOOKUP Excel Template here – Alternatives to VLOOKUP Excel Template

I have a simple zone-wise sales table.

Index Function Example 1-1

I can fetch the data by using VLOOKUP, but here I will use the INDEX formula. Open the formula in the E2 cell.

Index Function Example 1-2

Now select the required result column values. Here my required values are in from B2 to B5. I will select the range as B2:B5 and lock it.

Index Function Example 1-3

Now I need to mention the row number. For the South region, the row number is 2.

Now I need to mention from which column we are trying to fetch the data. In this case, it is the first column because we have selected only one column in the array argument.

Index Function Example 1-4

INDEX will fetch me the data for the SOUTH region.

Index Function Example 1-5

Wow!!! INDEX has done the trick for me.

However, the problem will start now. When I copy and paste the formula to the below cells, I need to change the row number as the cell changes. In the current cell, the row number is 1, and when I moved to the next cell, it has to be 2. INDEX does not take the row incremental number automatically.

Index Function Example 1-6

We can automate this task by using the MATCH function.

MATCH Function – Example #2

MATCH Formula

It will return the row number of a selected value in the supplied range.

  • Lookup Value: What is the lookup value you are trying to find the row number?
  • Lookup Array: From which range you are trying to find the row number.
  • Match Type: What kind of result you want to search. Whether it is the Approximate match below the lookup value (1) or Exact match (0), or an approximate match above the lookup value (-1).

Now I will use the same data from the above example. I will try to look out for the row number for each region.

MATCH Function Example 2-1

Open the formula in the E2 cell and select the lookup value as the D2 cell value.

MATCH Function Example 2-2

Now lookup array is from which table you are trying to know the row number. Our array table is from A2 to A5.

MATCH Function Example 2-3

The match type should be exact so that I will mention 0 as the argument.

MATCH Function Example 2-4

I got the row numbers for each region.

MATCH Function Example 2-5

Drag and drop the formula to the remaining cells.

MATCH Function Example 2-6

Limitation of VLOOKUP – Example #3

VLOOKUP always work from left to right. Now, look at the below data.

VLOOKUP Alternatives Example 3-1

I need to extract salary data from the range A1 to D21. I can do this by applying a VLOOKUP function.

VLOOKUP Alternatives Example 3-2

The Result will be as below:

VLOOKUP Alternatives Example 3-3

Drag and drop the formula to the remaining cells.

VLOOKUP Alternatives Example 3-4

At this point in time, VLOOKUP is doing the job for me. But now look at the below data structure.

VLOOKUP Alternatives Example 3-5

Based on the EMP ID, I need to extract the data, but the EMP ID is there at the very right end of the actual data. VLOOKUP starts to fade away in these cases.

So we have an excel alternative to VLOOKUP called the INDEX & MATCH function.

How to Use Alternative to VLOOKUP in Excel?

An alternative to VLOOKUP is very simple and easy. Let’s understand how to use alternatives to VLOOKUP in excel with some examples.

INDEX + MATCH Function as Alternative to VLOOKUP – Example #1

Now take the same table from the above example.

VLOOKUP Alternatives Example 4-1

Open the INDEX formula in the H2 cell.

VLOOKUP Alternatives Example 4-2

An array is nothing but what is the result you are trying to find. Here we are trying to find the salary value, so select the entire salary range.

VLOOKUP Alternatives Example 4-3

Now we need to mention the row number. In the MATCH function example, we have learned MATCH function can give us the row number. So I will use a MATCH function inside the INDEX function.

VLOOKUP Alternatives Example 4-4

LOOKUP means on what basis I am trying to find the row number. Here for EMP ID, I am trying to find the row number, so the lookup value is EMP ID.

VLOOKUP Alternatives Example 4-5

Now, look up array is nothing, but it is the main table lookup value column.

VLOOKUP Alternatives Example 4-6

Now finally, the match type should be the exact match, so mention 0 as the value.

VLOOKUP Alternatives Example 4-7

The Result will be as below:

VLOOKUP Alternatives Example 4-8

Yessssss!!!!!!! INDEX + MATCH is working exactly like the VLOOKUP but can fetch the data from anywhere to anywhere.

Drag and drop the formula to the remaining cells.

VLOOKUP Alternatives Example 4-9

LOOKUP Function as Alternative to VLOOKUP – Example #2

LOOKUP Formula

LOOKUP itself is a built-in function in excel.

  • Lookup Value: It is the value on the basis you are looking for the result.
  • Lookup Vector: It is the Lookup value range in the main table.
  • Result Vector: It is the result column in the main table.

Take the same data from the previous example.

VLOOKUP Alternatives Example 5-1

Open the LOOKUP formula in the H2 cell and select the lookup value as G2.

VLOOKUP Alternatives Example 5-2

Now we need to select the lookup vector as D2 to D21 in the main table.

VLOOKUP Alternatives Example 5-3

Finally, the result vector should be the result column we are trying to extract B2 to B21.

VLOOKUP Alternatives Example 5-4

After entering the formula, hit the enter key, and we will get the result.

VLOOKUP Alternatives Example 5-5

Drag and drop the formula to the remaining cells.

VLOOKUP Alternatives Example 5-6

Things to Remember about Excel Alternatives to VLOOKUP

  • VLOOKUP can only work from left to right.
  • MATCH function will return the row number.
  • INDEX + MATCH and LOOKUP functions do not require column number; unlike VLOOKUP, require column number to fetch the data even though the required column is already selected.
  • Data structure does not matter for INDEX + MATCH and LOOKUP functions. But for VLOOKUP, data structure matters.

Recommended Articles

This has been a guide to Alternatives to VLOOKUP in Excel. Here we discuss the examples of excel alternatives to VLOOKUP such as INDEX, MATCH, and LOOKUP, along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. VLOOKUP Function in Excel
  2. LOOKUP in Excel
  3. IFERROR with VLOOKUP in Excel
  4. VLOOKUP Function in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Alternatives to VLOOKUP Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Alternatives to VLOOKUP Excel Template

EDUCBA

डाउनलोड Alternatives to VLOOKUP Excel Template

🚀 Limited Time Offer! - ENROLL NOW