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
Corey Becker commented
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.
Wyn Hopkins commented
=Extract(LookupValue,LookupColumn/Row,OutputColumn/Row,optional Iferror Value)
Kenneth Barber commented
I think that at that point, INDEX and MATCH or PowerPivot should be used. PowerPivot actually links columns for implicit lookups.
So something like this, where you don't have to even have the thing you're looking for in the first column of your lookup table?
= TVLOOKUP (A1, [PersonName], [NumberOfThings], false )
ID | PersonName | Age | NumberOfThings | City
Ray Panko commented
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.