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.
Wyn Hopkins
shared this idea
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.
Joe McDaid
128 comments
-
Michael
commented
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.
-
Kenneth Barber commented
I've compiled all VLOOKUP posts, as of now, below. I put an asterisk by the ones that I think are the most important.
*Multiple criteria
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10552227-vlookup-function-for-multiple-criteria*Look to rows above or columns to the left
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9117904-extend-vlookup-and-hlookupFuzzy lookup
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9294741-advance-vlookup*Integrated error handling
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10552821-integrate-error-handling-value-on-error-for-functi*Return Nth occurrence
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10633782-vlookup-for-2nd-3rd-etc-occurrance*Exact match with sorted search
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10623393-vlookup-with-exact-match-and-sorted-dataSuggest table columns if used on a table
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9301302-tvlookup-table-vlookupOption to ignore text formatting of numbers
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10633707-vlookup-should-treat-the-lookup-value-as-it-appear*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.
-
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!
Current
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Proposal #1
=LOOKUP_1(lookup_value, table_array, [lookup_idx_num], [return_idx_num], [row_or_col], [range_lookup])Where...
[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.
Proposal #2
=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)
-
I think that at that point, INDEX and MATCH or PowerPivot should be used. PowerPivot actually links columns for implicit lookups.
-
Andi
commented
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 )
Lookup table:
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.