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.
And that’s a wrap! XLOOKUP has been released to production. All Office 365 users in the Monthly Channel should have it in their Excel now. Users in the Semi-Annual Channel should have it starting in July.
You can read about XLOOKUP here: https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
Thanks for voting! Make sure you move your votes to another request.
I agree Tables makes life much easier, but depending on where the information comes from the column headers can be meaningless. I am constantly using MSQUERY to farm information from our AS400 back into an Excel Table but I still define the names of table to make my formulas more meaningful/understandable. Defining the names may take an extra couple minutes up front but in my opinion being able to understand the formula for future modifications makes it well worth the time spent.
Anonymous, your SCHLOOKUP notation can get shorter still if we use MATCH's -1/0/1 instead of VLOOKUP's TRUE/FALSE for the last parameter. What is SCH by the way?
I disagree with the trade-off that you make (i.e. assuming the first column rather than using an extra parameter). In my work, I have sites as the column being searched. However, these sites fall into more generalized categories, which are also in the table. Naturally, I want the most general information on the left and the most specific information on the right, and so the sites are not the first column.
Harlan Grove commented
The Table idea is good, but Tables would be better if they also had key fields which didn't need to be first column. That might allow for Table lookups like
Exact matching would always be assumed, but the key field would need to have only distinct values, so could be indexed, so key lookups could be fast.
Generalized lookups would be nice too, but nicer still would be Google Sheets' FILTER function. With that, the need for LOOKUP, VLOOKUP, HLOOKUP and INDEX+MATCH disappears.
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