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.
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:
- 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.
I have a simple zone-wise sales table.
I can fetch the data by using VLOOKUP, but here I will use the INDEX formula. Open the formula in the E2 cell.
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.
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 will fetch me the data for the SOUTH region.
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.
We can automate this task by using the MATCH function.
MATCH Function – Example #2
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.
Open the formula in the E2 cell and select the lookup value as the D2 cell value.
Now lookup array is from which table you are trying to know the row number. Our array table is from A2 to A5.
The match type should be exact so that I will mention 0 as the argument.
I got the row numbers for each region.
Drag and drop the formula to the remaining cells.
Limitation of VLOOKUP – Example #3
VLOOKUP always work from left to right. Now, look at the below data.
I need to extract salary data from the range A1 to D21. I can do this by applying a VLOOKUP function.
The Result will be as below:
Drag and drop the formula to the remaining cells.
At this point in time, VLOOKUP is doing the job for me. But now look at the below data structure.
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.
Open the INDEX formula in the H2 cell.
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.
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.
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.
Now, look up array is nothing, but it is the main table lookup value column.
Now finally, the match type should be the exact match, so mention 0 as the value.
The Result will be as below:
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.
LOOKUP Function as Alternative to VLOOKUP – Example #2
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.
Open the LOOKUP formula in the H2 cell and select the lookup value as G2.
Now we need to select the lookup vector as D2 to D21 in the main table.
Finally, the result vector should be the result column we are trying to extract B2 to B21.
After entering the formula, hit the enter key, and we will get the result.
Drag and drop the formula to the remaining cells.
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 –
