Make a simple safer version of VLOOKUP and INDEX MATCH
The majority of Excel users use VLOOKUP to get exact matches from a row or column, It would be good if they all used INDEX MATCH but it's more complex so how about this simpler version for the masses.
=GETMATCH(ReferenceToMatch, MatchColumn/Row, GetColumn/Row, Optional Value if no match)
If seen a few other references to updating VLOOKUPS and there are lots of opportunities, but it would be good to address the simple most widely used issue first.
Thanks for the votes! Based on your feedback/votes we’ve just announced the new XLOOKUP function. You can read more about it here: https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
Hi Daniel, that does make the formula easier to understand after the fact. I'd also fully recommend converting your source data to Tables wherever possible as INDEX MATCH and Tables are brilliant together giving you that meaningful formula and none of the overhead of having to set up named ranges or expand the ranges as more data is added.
I agree that some people struggle with the concept of the Index/Match function. I have found that if you first teach them how to define names in the name manager that they have a much easier time grasping the concept. Example is if you have a sheet with 1000 rows and multiple columns and lets say you want to return a number (Total sales to date) from column "C" by matching a Name (sales person) in Cell "D1" by looking for Name in column "A". The formula with undefined names would be =INDEX(C1:C1000,MATCH(D1,A1:A1000,0)) Meaningless for a new user but if you define the names =INDEX(SALESPINDEX,MATCH(SALESPMATCH,SALESTOTLOOK,0)) the formula is much easier to comprehend for the newer user.
Scott, I agree that once people see it they prefer to use it, but as an Excel developer and trainer I regularly see first hand how most excel users struggle with it
I developed this trick to help them, but we shouldn't need a trick. https://www.linkedin.com/pulse/stop-using-vlookup-wyn-hopkins
Scott Atkinson commented
The Index Match function once taught to an Excel user is nearly always used instead of Vlookup, it is a relatively easy to use formula.
If you're looking up a value (A5) in the first column of a table (Table1) and want a particular column by name, this works. But its confusing to interpret.
A simpler function that takes the minimum # of parameters might be:
Even better, if this lookup is in column D, this formula could be copied down...and to other columns as long as same column names are used both tables:
I wouldn't mind something like this.
Nice and short.
You're right Kenneth, I tried to delete the old one but wasn't able to. Having read various other suggrstions on VLOOKUP andI NDEX MATCH (a lot of which are very good suggestions for the more advanced Excel users) I think we need to step back and take a look at how a regular Excel user sees formulas and try and make as user friendly a one as possible, hence my slight revision to to my earlier terminology
This is just an elaboration of your previous post:
I like the idea of MINDEX a bit better than this one, but that's just my opinion.
jason chroman commented
This one is important.
You've correctly pointed out that the the sacred way that Excel handles lookups has multiple issues:
1. VLOOKUP can't lookup left, HLOOKUP can't lookup above.
2. VLOOKUP can't handle multiple conditions
3. VLOOKUP breaks when you add a column in the middle of a lookup table, because the function requires an offset field.
4. And =LOOKUP can't do an exact match. That's the real problem in my book, as it requires an ordered list, which is common in financial modeling, but not in data analysis.
5. INDEX/MATCH is pretty good, but it requires you to write two functions when one should suffice.
I kind of like your proposal #2. I think to implement that, it might require an exact lookup.
But I think we can make it simpler. 9 times out of 10, I do simple, exact lookups, and if =LOOKUP did an exact lookup, I would have no need for VLOOKUP and little need for INDEX/MATCH.
So, I'd propose an =ELOOKUP formula, which is the same as =LOOKUP but it does an exact lookup. =ELOOKUPS could be the essentially the same as your LOOKUP_2 but with multiple criteria.
While TVLOOKUP would shorten the notation, you can get an equivalent formula using INDEX and MATCH.
INDEX(LookupTable[Return Column],MATCH("lookup value",LookupTable[Lookup Column],0))
I know that this looks long, but it's only because of the table and column names. When this technique is used on non-table ranges, it looks like this:
David Aldred commented
Andi's approach is exactly what is needed.
Currently, I will do something like this;
which will work, if the lookup value is in the first field, but otherwise you have to use a standard vlookup.
I think that part of the reason why INDEX and MATCH are hard to grasp for most of us is that we're exposed to VLOOKUP first. Then we have to get out of the mentality of "find the match and move over" and turn it into "find the match and return a value in the same position".
I guess you have a point. INDEX(,MATCH()) is common enough to justify a simplification, even if it reduces its useful scope. It's similar to how most of us will use MAX whenever we can instead of LARGE, or SUMIFS instead of AGGREGATE.
Would I use MINDEX if it were implemented? Of course! But I'm also perfectly happy with using INDEX(,MATCH()) instead.
Anthony Newell commented
Sadly, I suggested it many years before that but it never got any traction. It's good to see MS more receptive these days. Remember, it's about trying to things slightly easier for the uninitiated. I'm sure you can pour cold water on my other suggestions so knock yourself out!
It's a bit shorter I guess.
However, the advantage of separate functions is that you can reuse the MATCH portion over multiple INDEX functions.
You're also not the first to suggest this.
Anthony Newell commented
Match & Index are so powerful in preference to using VLookup. In the same vain as the new 'IFS' function I think MS should release a new function called 'MINDEX' which wraps both functions together and makes it easier for the uninitiated. Basic users often find it hard to understand how to use Match & Index, even less so in combination
I was hoping for something simple that the vast majority of Excel Users could understand instantly.
Your post quickly got superseded by this one:
You also missed some important features, like exact/sorted match.
In summary, it describes a FILTER function that captures all of the "starred" functionality in my previous comment, which eliminates the need for VLOOKUP and the database functions. Elimination of the SUMIF family is less obvious, but you would go SUM(FILTER(0,,,,)). FILTER is based on SQL queries and would require Excel to have faster array handling, which I also gave suggestions for.
I'll leave it to an admin to decide if my suggestion is better off as a comment here
Kenneth + others,
Good to see the interest in developing the lookup functions.
Just one comment to the Vlookup scenarios. Many of the ideas looks good but we are still left with the shortcoming regarding speed. To many formulars with Vlookup slow down calculations compared to index/match.
Please look at the speed issue when calculating. Why have vlookup when you have index/match. Suggestion make new function that takes the best from both
Corey Becker commented
@Kenneth, Good list. I'm guessing it would get too complicated to incorporate AND/OR, especially when it's easy to just use multiple functions added together. I don't know how you could implement that in a simple way within a single function. Interested to see ideas though.
I also have a deep-seeded hatred for vlookup. I usually try to put all data in tables and add new fields for lookups. I will add a bit field that checks whether each row meets all the conditions and then my index/match or sumif or other function can just filter on that one column.
I've compiled all VLOOKUP posts, as of now, below. I put an asterisk by the ones that I think are the most important.
*Look to rows above or columns to the left
Suggest table columns if used on a table
Option to ignore text formatting of numbers
*In the Nth occurrence link, I made a comment about having the lookup function return all matches so that INDEX can be used to pick the Nth occurrence. An alternative would be to integrate N into the lookup, much like the error handling suggestion.
*For the multiple criteria link, I should add that it should NOT be like SUMIFS. We should have AND and OR criteria.
I don't know whose dumb idea it was to come up with VLOOKUP anyways. It's so unintuitive compared to INDEX & MATCH once you know how both they and VLOOKUP do their lookup. The only reason why INDEX & MATCH aren't as popular is because they are 2 functions instead of 1.