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.
I do not see a significant difference between your example
and the INDEX function I normally use.
My typical formula is
MATCH(CurrentDate, Data!$1:$1, false),
MATCH("abc", Data!$A:$A, false)), 0)
Other than the terminology, what is the added benefit...?
Cristian Mendes Firmino commented
I really like it! I've always created my own function to solve this problem, but if there were a native function, it would be better.
I like it! Simplicity is always wonderful.
A.C. WILSON commented
This has also been suggested in other posts in this forum.
I favor it, because INDEX(,(MATCH(... is too complex/obtuse for some users, and VLOOKUP(... lacks power & precision.
Oz du Soleil commented
A middle ground between VLOOKUP and INDEX/MATCH would be really powerful.
Re VLOOKUP and table parameters, that's pretty much how INDEX MATCH works already. That's another benefit highlighted of why INDEXMATCH is more useful than VLOOKUP.
I'm not in favor of GETMATCH. There is no reason to add yet another function when VLOOKUP is so widely used and understood. I'd like to see VLOOKUP extended to use defined tables where the second parameter is the table name and the third parameter is the column name. A third parameter of 3 is cryptic where "Price" is meaningful. Especially when you are viewing someone else's workbook.
After testing this idea out with around 200 Excel course attendees over the last year 2 things have become clearer. 1. People really like this idea. 2 the name GETMATCH might be better named LOOKUPMATCH in that it then captures elements of familiarity to the VLOOKUP AND INDEX MATCH users.
The sole reason (in my view) that the majority of users use VLOOKUP is that INDEX MATCH is the "wrong way round". Fix that with this suggestion and we'll make Excel even better
No VBA required, existing functionality already exists, just the order of parameters need changing from this
=IFERROR ( INDEX ( GetColumn ), MATCH (ReferenceToMatch, MatchColumn,0)), "TEXT IF ERROR")
Can you please post an example of your GETMATCH function, with the VBA that will drive it?
Hi Anthony, I see what your saying but that's not in keeping with my hope of making a simpler version of INDEX MATCH. My suggestion is targetted at the 95% of users who need single column (occasionally row) lookups.
Antony Pihut commented
GETMATCH can be improved like SUMIF was upgraded to SUMIFS.
Original INDEX/MATCH combination can be used for 2 dimensional search in a table when you indicate a MATCH for a Row and a MATCH for a Column. In that case we would need 2 references to match. What about multidimensional table(cube)?
I would suggest a closer to current INDEX syntax:
=INDEXS(TableToSearchIn, R1CriteriaRange, R1Criteria, R2..., C1CriteriaRange, C1Criteria, C2...). R for Rows to match downand C for Columns to match across. Currently it must be an array formula to achieve this.
Great to be up to number 5 in the rankings, please encourage others to vote.
I've also pulled together a video showing how INDEX MATCH currently works so well with tables but I end up using an autocorrect trick in the absence of this requested new formula
Mike, I agree about the fact that this will also provide a simpler alternative to HLOOKUP. Unfortunately I am blocked from making any changes to my suggestion now that people are voting for it (Thanks everyone by the way!). I can't even change my spelling mistake :)
Just to be clear, include HLOOKUP in the description of what it will replace.
Agreed. I prefer use MATCH... Go to GETMATCH
V Lookup old style of matching. Index match is modern formula.
Go Index Match.
I'm indifferent on the use to either the said functions. In my opinion, SIMPLICITY is the key to deciding which function to use. Go go GETMATCH !
David Olson commented
Personally, I much prefer INDEX MATCH vs. VLOOKUP, but I would love an easier solution.