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.
Vlookup alternative for neophytes to match up datat from one spreadsheet to another based on a common data link. End-users scream at us that Vlookup is impossible unless they are programmers. I agree Vlookup is just way too complicated.
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
There are a couple other similar ideas but I have a couple specific suggested solutions that I hope are considered when a solution is devised. Thanks!
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=LOOKUP_1(lookup_value, table_array, [lookup_idx_num], [return_idx_num], [row_or_col], [range_lookup])
[lookup_col_num] is the column to search for lookup_value. This field is optional with a default of 1.
[return_col_num] is the column that the result is pulled from. This field is options with a default of the last column in the table_array.
[row_or_col] may be a little more controversial but I don't understand the benefit of having two different functions that basically do the same thing. Have it default to VLOOKUP with and option to switch to HLOOKUP.
I would argue that the new lookup function would be even simpler than the current version with only two required inputs and a large improvement to flexibility. However, you are still somewhat limited in that you need a contiguous table_array so I have a second proposal that's a little more complicated but should be familiar to those using some other popular functions in excel.
=LOOKUP_2(return_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
This should look familiar. It's the sumifs/countifs function but instead of counting/summing, it simply returns the first result matching all the criteria. You could also throw an optional [instance_num] in there like the substitute function has.
=Extract(LookupValue,LookupColumn/Row,OutputColumn/Row,optional Iferror Value)
VLOOKUP is a massive source of errors. Instead of putting in column numbers when VLOOKUP is done on a table, why not have a function that specifically works on tables. With tables, drop-down lists of columns could be selected from instead of how many columns over Excel should look.
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.
Agreed. I use Index Match all the time, rather than Lookup, but this would definitely simplify matters.
Harlan Grove commented
PowerQuery is great for pulling data from external sources. It's overkill and slower getting data already within the same workbook as the formulas accessing that data.
Why use match/lookup when you have Power Query :)
Hi Excel Team, any feedback for us on this? Thanks
And have an option to search for multiple columns. So first argument (ref to match) could be a 1x2 range, and the second (match column) would be a nx2 range.
Corey Becker commented
I think it would be cool if there was an input for which match to return (0 to return an array of the matched values, -1 to return the last occurrence, 1+ to return the corresponding match). Hopefully that would be an optional input and wouldn't hurt performance for most use cases.
Charles Williams commented
I agree that a simple form of exact match lookup is needed that is optimised for both unsorted and sorted data. Performance can be significantly improved for linear search by storing the last index found and rechecking it next time. My suggested implementation of the simple form (MEMLOOKUP and MEMMATCH) handles both sorted and unsorted data, always gives an exact match and allows the column to be either a number (for compatibility with existing functions) or a column label. More complex variations of LOOKUPS are handled by the other members of the AVLOOKUP family (the function gets too complex to use if you try to satisfy all the options with a single function). You can try out these implementations - download from http://www.decisionmodels.com/Downloads/InstallFxlV3.zip
Kenneth Barber commented
For your "re tangent", the =+ notation isn't the fault of Lotus 1-2-3 users. There's nothing wrong with using + to start a formula. The question is: why does Excel keep the +? I think that you could make a suggestion out of that.