In the world of Excel, people often talk about which functions are best for finding and getting data. Two popular ones are VLOOKUP and INDEX MATCH. VLOOKUP has been popular for a long time because it’s simple and easy. But INDEX MATCH is gaining popularity because it’s more flexible and powerful. Understanding why INDEX MATCH is often seen as better than VLOOKUP can really improve how you work with data in Excel. It can make your work faster and more accurate. Let’s look at why advanced Excel users prefer INDEX MATCH and how it can make you better at managing data in Excel.
Table of Content
- What is INDEX MATCH in Excel
- How to Use the INDEX Formula in Excel
- How to Use the MATCH Formula in Excel
- How to Combine INDEX and MATCH
- What is VLOOKUP in Excel?
- Estimated match in VLOOKUP
- INDEX MATCH Vs. VLOOKUP
- Comparison Table of VLOOKUP vs INDEX MATCH
- Conclusion
- FAQs on Why INDEX MATCH is Better Than VLOOKUP in Excel
What is INDEX MATCH in Excel
The Following Formula is the blend of two capabilities in Excel: INDEX and MATCH. Consolidated, the two equations can gaze upward and return the worth of a cell in a table in light of vertical and level standards.
=INDEX() returns the worth of a cell in a table in light of the segment and column number.
=MATCH() returns the place of a cell in succession or segment.
How to Use the INDEX Formula in Excel
The following is a table showing individuals’ names, levels, and weights. We need to utilize the INDEX equation to look into Thanish’s level, here is an illustration of how to make it happen. Following these means,
- Type “=INDEX(” and select the region of the table, then, at that point, add a comma
- Type the column number for Thanish, which is “4,” and add a comma
- Type the section number for Height, which is “2,” and close the section
- The outcome is “5.8.”
How to Use the INDEX Formula in Excel
How to Use the MATCH Formula in Excel
Staying with a similar model as over, we should utilize MATCH to sort out what column Thanish is in.
Following these means,
- Type “=MATCH(” and connection to the cell containing “Thanish”… the name we need to turn upward.
- Select every one of the cells in the Name section (counting the “Name” header).
- Type zero “0” for a definite match.
- The outcome is that Thanish is in column “4.”
How to Use the MATCH Formula in Excel
How to Combine INDEX and MATCH
Presently we can take the two MATCH equations and use them to supplant the “4” and the “2” in the first INDEX recipe. The outcome is an INDEX MATCH equation.
Following these means,
- Cut the MATCH equation for Thanish and supplant the “4” with it.
- Cut the MATCH equation for Height and supplant the “2” with it.
- The outcome is Thanish’s Height is “5.8.”
- Congrats, you currently have a powerful INDEX MATCH equation!
How to Combine INDEX and MATCH
What is VLOOKUP in Excel
VLOOKUP means ‘Vertical Lookup’. A capability makes Excel look for a specific worth in a segment (the purported ‘table cluster’), to return a worth from an alternate section in a similar column. This article will show you how to utilize the VLOOKUP capability.
A VLOOKUP capability exists in 4 parts,
- The worth you need to turn upward;
- The reach wherein you need to find the worth and bring esteem back;
- The quantity of the section inside your characterized range, that contains the bring esteem back;
- 0 or FALSE for an accurate coordinate with the worth you are searching for; 1 or TRUE for an estimated match.
Syntax: VLOOKUP([value], [range], [column number], [false or true])
In our model, we have a rundown of Names, Height, and Weights. We need to find the Height of Sai rapidly in this table. In the first place, select a cell in which you need to distribute the Height:
What is VLOOKUP in Excel
Along these lines, in our chosen cell we begin composing: =VLOOKUP( :
VLOOKUP in Excel
Then, at that point, we select the worth we need to look into, for this situation, it’s ‘Sai’ in cell F3,
VLOOKUP
Next, we enter ‘,’ and select the reach (or table cluster) in which we need to find the query esteem and the bring esteem back. For this situation it is range (B2:D8):
VLOOKUP Formula
Then, at that point, we enter ‘,’ and the quantity of the section inside the characterized range wherein we need to search for the bring esteem back. For this situation, it is segment 2, trailed by ‘,’ and ‘0’ or Misleading for a definite coordinate with the query esteem ‘Sai’:
VLOOKUP Formula
At the point when we press enter, we get the comparing cost from the column that holds esteem ‘Sai’ inside the chosen table exhibit:
VLOOKUP Formula
In this model we just have a little rundown of names, however, envision you have a considerable rundown of information and you wish to utilize a specific worth somewhere else in your bookkeeping sheet. Utilizing VLOOKUP (and HLOOKUP) the client just needs to change a specific worth in one worksheet and it will consequently be changed in any remaining significant spots. Be that as it may, imagine a scenario in which we’re unsure about the query esteem. This is the point at which the estimated match may be valuable.
Estimated match in VLOOKUP
At the point when we enter the word ‘Sia’ in our query esteem cell G3, the VLOOKUP capability as we planned gets confounded, because it can’t track down this worth in the table exhibit. It returns the standard Excel blunder #N/A:
Estimated match in VLOOKUP
Be that as it may, when we change the ‘0’ (or ‘False’) part of our VLOOKUP capability to ‘1’ or ‘Valid’:
=VLOOKUP(F3,B2:D8,2,1)
Presently we have requested that the capability search for an estimated coordination with ‘Sia’. On the off chance that it can’t find the specific match, it will search for the nearest match to the worth ‘Sia’, not as much as ‘Sia’, which is ‘Sai’:
Estimated match in VLOOKUP
INDEX MATCH Vs VLOOKUP
Dynamic Column Reference Leads to Fewer Errors
The critical contrast between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static section reference while INDEX MATCH utilizes a powerful segment reference. With VLOOKUP, the vast majority will include a particular, static number to show which section they need to get back from. At the point when you use INDEX MATCH, the recipe permits you to physically pick which section you need to pull from. The explanation this prompts fewer blunders is that when you follow the INDEX MATCH linguistic structure, you click straightforwardly on the field containing the worth you need to return.
INDEX MATCH Vs VLOOKUP
With the VLOOKUP language structure, you determine your whole table exhibit, AND THEN you determine a section reference to show which segment you need to pull information from.
INDEX MATCH Vs VLOOKUP
It’s a little contrast, however, this extra step without a doubt prompts more blunders. This mistake is particularly predominant when you have a huge table cluster and need to outwardly count the number of sections you need to move over. At the point when you use INDEX MATCH, no such counting is required.
The following two benefits of utilizing INDEX MATCH likewise come from the way that VLOOKUP requires a static segment reference input for the qualities you need to return. If it’s not too much trouble, note that you could get around the static reference issue by utilizing an equation inside the VLOOKUP punctuation, making a mix recipe like VLOOKUP MATCH or VLOOKUP HLOOKUP. Notwithstanding, it’s a lot more straightforward just to learn INDEX MATCH and gain every one of the extra advantages past having dynamic section reference.
Insert Column Immunity
The best advantage of utilizing INDEX MATCH over VLOOKUP is the way that, with INDEX MATCH, you can embed segments in your table exhibit without misshaping your query results. Any time you work with a huge dataset, there’s a decent opportunity you’ll have to return to alter our sections and possibly embed another segment. With VLOOKUP, any embedded or erased section that uproots your return values will change the aftereffects of your equations.
Here, we’ve arranged the equation to pull the State esteem from our information table. Since it is a VLOOKUP recipe, we have referred to the fourth section.
Insert Column Immunity
If we insert a column in the middle of the table array, the new result is now “Bangalore”; we are no longer pulling the correct value for State and must change the column reference.
Insert Column Immunity
INDEX MATCH as an embed section insusceptibility, so you can embed and erase segments without stressing over refreshing each related query equation.
Easier to Drag and Copy
While working with enormous datasets, it’s uncommon to compose only a solitary query recipe; you’re probably going to have to drag and duplicate your equation to numerous cells to play out different queries. For instance, suppose that for a particular ID, I need to return a progression of values from my table precisely as per the pattern in which they show up in the table. At the point when I attempt to drag and duplicate the standard VLOOKUP equation across (with a reference lock on both the query esteem and the table cluster), the query doesn’t work since it simply pulls a similar incentive for every passage. This lack is again brought about by VLOOKUP requiring a particular section reference input for your bring values back.
Easier to Drag and Copy
With INDEX MATCH, since you can set the return section to drift (basically by not reference locking it) the return segment will move as you duplicate your equation over, giving you the various fields as they show up.
Easier to Drag and Copy
No Array Restriction
One more key impediment of VLOOKUP is that it expects you to determine a square table cluster wherein your segment reference can’t move past. The key circ*mstance when this turns into an issue is the point at which you add another field to your dataset past your unique table cluster. Consider the model beneath:
Assuming we add another segment to this dataset, we can’t utilize my unique VLOOKUP recipe to pull values from that new section. If we change my section reference to “6,” the recipe returns a mistake because my table is just 5 segments wide. To make the recipe work, we need to refresh the table exhibit we determined each time we added another segment.
With INDEX MATCH, you don’t have to determine a table cluster and hence don’t deal with this issue.
Right to Left Lookup
One of the vital advantages of utilizing INDEX MATCH is having the option to make query keys on the right half of your information table. New query keys are for the most part made by running estimations on fields inside your unique dataset. These keys address your query segment inside the INDEX MATCH language structure. While utilizing INDEX MATCH, you can add these new query keys to the right half of your table and play out an option to pass on the query to pull the qualities you need to return. The INDEX MATCH punctuation doesn’t mind whether your query segment is on the left or right half of your bring section back.
Reasons why INDEX MATCH is better than VLOOKUP
VLOOKUP | INDEX MATCH |
VLOOKUP utilizes the static information reference while looking into the qualities. | INDEX MATCH utilizes powerful information ranges while looking into the qualities. |
Embedding or Deleting a section influences the VLOOKUP result. | Embedding or erasing a section doesn’t influence the INDEX MATCH result. |
VLOOKUP must be utilized for looking into values from Left to Right. | INDEX MATCH can look into the qualities from Left to Right as well as Right to Left. |
VLOOKUP just can query through vertical lines, for example, segments, and not through columns. | INDEX MATCH can query values through lines as well as segments. |
VLOOKUP has a breaking point for lookup_value size. It ought not to be surpassing 255 characters. | INDEX MATCH has no restriction of size or length for matching qualities. |
VLOOKUP isn’t well disposed of with regards to duplicate and drag. | INDEX MATCH is more well disposed of with regards to duplicating and dragging the equation across different cells. |
VLOOKUP is straightforward as well as recollect in examination with INDEX MATCH. | INDEX MATCH is difficult to comprehend as well as recollect because of mind-boggling nature consolidates two capabilities/formulae. |
VLOOKUP isn’t generally as quick as the INDEX MATCH when we are managing enormous informational collections. | INDEX MATCH is generally quicker than the VLOOKUP. |
Conclusion
In conclusion, the transition from VLOOKUP to INDEX MATCH represents a significant leap in Excel proficiency. While VLOOKUP may offer simplicity, INDEX MATCH outshines it with unparalleled flexibility, power, and efficiency. With its ability to handle dynamic data, perform complex searches, and provide superior accuracy, INDEX MATCH emerges as the undisputed champion for advanced Excel users. By embracing INDEX MATCH, you unlock a world of enhanced data management capabilities, revolutionizing your workflows and elevating the quality of your analyses. Make the switch today and experience firsthand the transformative impact of incorporating INDEX MATCH into your Excel arsenal.
FAQs – INDEX MATCH is Better Than VLOOKUP in Excel
Can INDEX MATCH handle multiple criteria lookups?
INDEX MATCH is capable of handling multiple criteria lookups by employing multiple MATCH functions together. This feature enables you to conduct intricate searches and extract precise data based on multiple conditions.
What are the limitations of INDEX MATCH ?
Although INDEX MATCH is a powerful feature the only problem with this Function is that it may be more complex to use than VLOOKUP, mainly for beginners. You should pay more attention while writing the formula.
Can we replace all LOOKUPS with INDEX MATCH?
INDEX MATCH is a versatile alternative to VLOOKUP, there’s no need to replace all VLOOKUPs. For simple vertical lookups, VLOOKUP is still effective and easier to use.
Next Article
XLOOKUP vs INDEX-MATCH in Excel