1. Excel Vba Index Match Listobject
2. Index And Match Vba Code

The lookup value in your VLOOKUP function is A4, whereas it is B3 in your INDEX/MATCH function. Your VLOOKUP function looks for a matching value in column C. The INDEX/MATCH function looks in the range A3:A86. Your MATCH function is trying to look in more than one column. MATCH can only look at one column.

During one of my Excel Training sessions, I was surprised to find out that the people were aware and quite proficient in using VLOOKUP, but most of them had no idea about INDEX/MATCH.

Here are some of the queries they had about Index/Match formulas:

• Is it a lookup and reference formula? (Yes! Maybe the best of the lot)
• Is it a new Excel 2016 function? (Hell NO!)
• Can it reference data between different worksheets or workbooks? (Yes)
• Is it better than VLOOKUP? (we will find out soon, keep reading)

I think the VLOOKUP PR team is doing a much better job than that of INDEX/MATCH.

Here in this blog post, I will try and compare these 2 formulas as objectively as I can (Disclaimer: I do have a penchant for the INDEX/MATCH combo).

Popularity

VLOOKUP takes this hands down. For some people, Excel formulas are synonymous with VLOOKUP. For this reason, a lot of people use VLOOKUP as it is better understood by other Excel users.

Verdict: VLOOKUP is a clear winner on popularity.

Ease of Use

VLOOKUP takes 3 arguments (fourth is optional) and is easier to understand as compared with INDEX/MATCH. INDEX & MATCH are 2 separate functions that take 3 arguments each and should be combined to do a lookup. While you may find INDEX/MATCH equally easy when you get a hang of it, a beginner is likely to gravitate towards VLOOKUP.

Verdict: VLOOKUP gets the point for it ease of use.

Flexibility

VLOOKUP is a great function but has a few limitations:

• It can not lookup and return a value which is to the left of the lookup value.
• It works only with data which is arranged vertically.
• VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column). You can make the column number dynamic, but if you planning to combine two or more functions, why not use INDEX/MATCH in the first place.

INDEX-MATCH combo can address all these issues. It can lookup and return a value which is to the left of the lookup value, and it can handle both horizontally and/or vertically structured data.

Also, since we are using MATCH to find the position of a column, it does not matter if you add or delete a column. The MATCH part of the formula would automatically take care of the position.

Verdict: INDEX/MATCH combo gives you a lot more flexibility than VLOOKUP.

Speed

The difference is hardly noticeable when you have small data sets. But if you have thousands of rows and many columns, this can be a deciding factor. Without reinventing the wheel, here is a post by Charley Kyd, where he clearly mentions:

“At its worst, the INDEX-MATCH method is about as fast as VLOOKUP; at its best, it’s much faster.”

That says it all!

Verdict: If speed is what you are looking for, INDEX/MATCH combo is the way to go.

Overall Verdict (Vlookup Vs Index/Match)

INDEX/MATCH is a powerful combo that has more flexibility and speed than the VLOOKUP formula. If you haven’t already adopted INDEX/MATCH as the way of life, it is about time you do it.

That being said, VLOOKUP is not going anywhere and is likely to remain as one of the most popular functions in Excel for ages to come.

Well, the debate is not exactly over (see the comments section). Fuel the fire – leave your 2 cents in the comments section.

You May Also Like the Following VLOOKUP Tutorials:

• Use IFERROR with VLOOKUP to Get Rid of #N/A Errors.
• Use VLookup to Get the Last Number in a List in Excel.

Excel Vba Index Match Listobject

Useful Excel Resources:

• Free Online Excel Training (7-part Video Course)
• 100+ Excel Functions (with examples and videos)

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

I have this formula on the first column in my sheet:

And it fits perfectly for what I want: look up on Plan2 (my databse) for the information on column B of Plan3 by matching the result by matching the header of table.

These encounters and weapons are random, but almost always cough up a nice little treat to make your character that bit tougher.You’ll know a Legendary item when you see it as it will begin with a prefix such as “Assassin’s” or “Hunter’s” and then the name of the item. Share on:Keep getting those random Legendary Weapons? Here’s all the different effects you can come across.Playing through Fallout 4 you’ll come up against Legendary Enemies that are a lot tougher to kill than the usual Raider, Bloatfly or Super Mutant.But take them down and you’ll be rewarded with a Legendary Weapon complete with a devastating effect. Fallout 4 legendary weapon effects.

What I want know is to translate this to a VBA that do the same thing. This is what I've tried so far:

When I try to run I get the message:

Compilation error: Reference is not valid.

And I get this line highlighted:

This is the first time that I try to translate a formula do a code in VBA so I really don't know what is going wrong.

Any suggestions will be appreciated.

Index And Match Vba Code

2 Answers

Try this:

Note: I've not tested your formula. Code shows how the formula result can be displayed using VBA.

MrigMrig

You used .match, but you don't have With statement before. You also don't use i in your formula. I guess it should be like this:

Egan WolfEgan Wolf

Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vbaexcel-formula or ask your own question.