Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

724 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Wyn Hopkins shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Thanks for the suggestion Wyn! Thanks also to all the contributors to this thread for the thoughtful discussion and debate.

Of the many lookup function requests, this is currently one of the highest ranking on UserVoice. Please keep the votes rolling in if you’d like to see this, it helps us prioritize new feature work.

JoeMcD [MS XL]

116 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Roy commented  ·   ·  Flag as inappropriate

    Oh, and to Mr. Wyn Hopkins!

    That Autocorrect idea is shear brilliance. It can clearly be useful in quite a few instances. I will use it myself and teach it to others, especially those who find Microsoft function definitions and help somewhat... abstruse... and therefore hard to figure out if one does not already know what each element is already which rather defeats the purpose in most cases. Expert "speech" ≠ Expert "teach" in quite a bit of their support site help.

    Just brilliant. Simple, complete, brilliant.

  • Roy commented  ·   ·  Flag as inappropriate

    Related, but no longer explaining those requirements, conceptually a formula is all about Excel providing building blocks with which one can nimbly piece together functions to solve one’s UNIQUE need of the moment. One needs general functions that do not attempt to solve world hunger on their own, but rather are small, exact, building blocks. HOWEVER… some things turn out to be more general and are needed often by everyone with just small, easily defined refinements to match the unique needs they of their moments. Lookups are one of these. So then a function of broader scope than, say, SUM(), is needed. Yes, just as there are workaround for summing, there are workarounds for lookups. But workarounds are hard to learn for many, and very hard to adapt to their needs because they do not understand them. They usually are not extensible for the same reason. If MS wants to sell to a wider audience, a simple function is needed. People who have a solid handle on the workaround can sit all as smug as their characters dictate and keep using their workarounds. Everyone else would use the new simple function. I can “go left” with VLOOKUP() using a negative number for the column (and in string functions as well). It takes some work, a wee more than INDEX/MATCH, but it’s not unbearable. I don’t use it often. Workarounds are a pain. I just want to enter a simple function modelled on the current VLOOKUP, mostly, and be done with it. So does literally everyone else I personally know.

    Lastly, and this is why Apple does so well with so many products, almost no one in the world wants to intimately know how their car works and to then tinker with it before, during, and after every use of the car. They just want to drive it. MS has to provide this “toaster” simplicity if they want their products used 10-100 times as much and as widely as they are now. A universal lookup function that takes easy care of all possible lookups would be quick to learn for 90% of its usages, and if one had to learn to use some of its features better to take advantage of its more advanced capabilities, one could do it that time, perhaps forgetting afterwards. It would, in a clear and obvious way, handle the basic, simple VLOOKUP() work we see so much of. And if you wanted to do more, you could just by widening your thinking about its parameters: “OHHHH… not just go over 3 columns, but to another sheet too… ohhhh… I never thought of that before…” No new function to learn and explain to anyone validating your spreadsheet, just a tweak to your usage of an old parameter friend.

  • Roy commented  ·   ·  Flag as inappropriate

    Folks, we simply need one function, fully enabled, to seek and find for us. Not limited by defining a limited, structured, lookup need, and then created but rather created to seek and find, then return a desired result.

    So, a function that

    1) Takes an input value
    2) Hunts in a given range for it finding all occurrences of it (either reporting a list of them, or evaluating further from the list)
    3) Allows one to choose a value OFFSET from it (or each) and return that value (or values). An allowance for returning an ADDRESS instead would be useful.
    4) Allows one to select one of many results (instance number) including a provision for “last” and counting back from “last.”
    5) Provides for a defined “error” result in the manner that IFERROR() does, or Excel’s own error result if that is not defined.

    To further explain the above:

    1) Pretty obvious, but included should be taking any result from a formula for this but “cleansing” the physical form of the formula’s return so that it does not itself cause an error as invalid input. (Ever use F9 to evaluate portions of a failed formula and found that the failure point (when the formula evaluates on its own does not cause failure when you F9 that portion and then hit Enter? That’s because the F9 process cleansed it somehow while Excel did not as it stepped through. The other way around happens too.)
    2) ANY range given it. Period. Single cell. A “Table” or a block of data we plebs think of as a table. A range built of subranges using the various joining methods available. Something CHOOSE() creates. And folks… 3-D ranges. “4-D” even, if one thinks of different files providing the 4th dimension.
    3) So, conceptually, picking the column to find a result in is currently a very simple, single value offset. And probably the offset almost always desired, honestly, but the function should not inherently limit itself to this. A single value, like 3 or -4, should default to a column 3 to the RIGHT or 4 to the LEFT, but a full 3-D offset should be available (or… 4-D).
    4) It should internally locate ALL occurrences of the lookup value. It should make this list available for output. It should use this list as a table for “sub-lookup” to return values (or their cell addresses if desired) quickly. One should be able to specify the “instance number” desired, or one chosen from a range of instances, the literal “last” instance, and of course, counting back from the end as well as forward from the beginning.
    5) One of the huge performance hits in Excel is the double evaluation inherent in a huge number of IF() usages. You know, IF “some long formula” is not an error then “some long formula.” IFERROR() improved that, though not useful for all instances since it just handles error results, but the idea here is to enter the lookup with any “some long formulas” involved as its sub-parts, and if it returns a null (as in “no instance of the lookup value was found” rather than one was found and the lookup location it returned was an empty cell), then return some defined result if one chose to define one rather than some Excel error. This way one need not wrap the lookup in an IF() test for this, evaluating it once for the IF() test, then returning it as a result if that returned TRUE and so having to evaluate it a second time.

    Call it ULOOKUP() (for Universla lookup.)

  • TAEJIN KIM commented  ·   ·  Flag as inappropriate

    I developed it and have used it with user defined function.
    if someone want to use this function, please refer to attached. and plated it permanently.

    and also helpful when you see the picture on my blog.

    please download it via my blog
    https://blog.naver.com/taejin1522/220943165840

    F= TJ(ReferenceToMatch, MatchColumn/Row, GetColumn/Row)

  • Greg commented  ·   ·  Flag as inappropriate

    ' nXLookup - VLOOKUP and HLOOKUP replacement function
    ' Parameters:
    ' - LookFor, What you are searching for
    ' - LookIn, the Range to search within
    ' - RowOffset, Simmilar to col_index_num in VLOOKUP except 0 is the first column and can use negative values
    ' - ColOffset, Simmilar to row_index_num in HLOOKUP except 0 is the first row and can ues neg values
    ' - Occurrence, Get the Nth Match
    ' - LoopValues, Causes the matching to wrap arround so that when set to true if there are 5 matches and you
    ' ask for the 7th then you'll get the 2nd
    ' - FindLookIn, 1 = xlValues - Search within the evaluated cell values
    ' 2 = xlFormulas - Search within the non-evaluated cell values
    ' 3 = xlComments - Search within the cell comments
    ' - LookAtWhole, true = match only cells where the whole contents match, false = partial match
    ' - SearchByColumns, true = search column by column, false = search row by row
    ' - SearchNext, true = top down when by columns or left to right when by rows
    ' false = bottom up when by columns or right to left when by rows
    ' - MatchCase, true = case-sensitive matching, false = non-case-sensitive
    ' - IsVolatile, true = updates when data updates, false = updates when formula cell is recalculated
    ' Note: I didn't implement the SearchFormat because it could be a bit tricky via a worksheet function, and
    ' would probably be easier to do on a case by case basis as needed
    ' Also: When FindLookIn = 2 or 3, you'll most likely want to pass False to LookAtWhole instead of defaulting to True.
    Public Function nXLookup(LookFor As Variant, LookIn As Range, Optional RowOffset As Long = 0, Optional ColumnOffset As Long = 0, Optional Occurrence As Long = 1, Optional LoopValues As Boolean = False, _
    Optional FindLookIn As Long = 1, Optional LookAtWhole As Boolean = True, Optional SearchByColumns As Boolean = True, _
    Optional SearchNext As Boolean = True, Optional MatchCase As Boolean = False, Optional IsVolatile As Boolean = True) As Variant
    Application.Volatile IsVolatile
    nXLookup = CVErr(xlErrValue)
    If FindLookIn < 1 Or 3 < FindLookIn Or Occurrence < 1 Then Exit Function

    Dim f As Range: Set f = LookIn.Find( _
    What:=LookFor, _
    After:=LookIn.Cells(IIf(SearchNext, LookIn.Cells.count, 1)), _
    LookIn:=Choose(FindLookIn, xlValues, xlFormulas, xlComments), _
    LookAt:=IIf(LookAtWhole, xlWhole, xlPart), _
    SearchOrder:=IIf(SearchByColumns, xlByColumns, xlByRows), _
    SearchDirection:=IIf(SearchNext, xlNext, xlPrevious), _
    MatchCase:=MatchCase _
    )
    nXLookup = CVErr(xlErrNA)
    If f Is Nothing Then Exit Function
    Dim FirstAddr As String: FirstAddr = f.Address
    Dim count As Long: count = 1
    Do Until (count = Occurrence) Or ((Not LoopValues) And f.Address = FirstAddr And count <> 1)
    Set f = LookIn.Find( _
    What:=LookFor, _
    After:=f, _
    LookIn:=Choose(FindLookIn, xlValues, xlFormulas, xlComments), _
    LookAt:=IIf(LookAtWhole, xlWhole, xlPart), _
    SearchOrder:=IIf(SearchByColumns, xlByColumns, xlByRows), _
    SearchDirection:=IIf(SearchNext, xlNext, xlPrevious), _
    MatchCase:=MatchCase _
    )
    count = count + 1
    Loop
    If count <> Occurrence Then Exit Function

    Dim MaxRows As Long: MaxRows = LookIn.Parent.Rows.count
    Dim MaxCols As Long: MaxCols = LookIn.Parent.Columns.count
    Dim OffROW As Long: OffROW = f.Row + RowOffset
    Dim OffCOL As Long: OffCOL = f.Column + ColumnOffset
    If Not (0 < OffROW And OffROW <= MaxRows And 0 < OffCOL And OffCOL < MaxCols) Then
    nXLookup = CVErr(xlErrRef)
    Else
    Set nXLookup = f.Offset(RowOffset, ColumnOffset)
    End If
    End Function

  • Bansundu M commented  ·   ·  Flag as inappropriate

    After struggling to learn this, i have to agree that your 60 seconds lesson holds. This makes a lot of sense and have no doubt that GetMatch will be great. I vote for the new function.

  • Anonymous commented  ·   ·  Flag as inappropriate

    I agree basically. However a new lookup function should replace all other lookup functions and be labeled as such (like a universal lookup function, xlookup as someone suggested, new lookup or whatever). That would be much easier for everyone than trying to remember which lookup functions have limitations for this and that and would eliminate lengthy forums like this if it worked correctly and intuitively. I don't mind Index match but why not call it something intuitive like ideas mentioned earlier?

    I'm not suggesting getting rid of old functions because they are still used in old spreadsheets, but help windows and lists could be updated to state those are only for backwards compatibility.

    I think one function with multiple criteria as someone else has suggested. The existing function, even if works by having to nest a bunch of things, is too difficult and time consuming to deal with and prone to problems because of the complexity. This is not about just the experts who develop spreadsheets every day. Having multiple criteria and options on one function can admittedly also be complex, but they can be optional and its easier than trying to figure out which of the many lookup functions to use every time you want to use them.

  • AHarris commented  ·   ·  Flag as inappropriate

    There has been a lot of interesting and usefull discussion on this topic - thanks everyone.

    My question is really simple:
    Your proposed function has 4 parameters, VLOOKUP/HLOOKUP has 4 parameters. Would this actually be simpler for beginners?

    (I last taught beginners about 15 years ago so your experience is probably more recent than mine. I am genuinely interested to hear your take on this.)

    Understanding the utility of "Optional Value if no match" does not seem simpler than understinding the "Optional match type" parameter. The utility of using it for rows or columns or mixing row/column like we can with INDEX/MATCH is good but is it simpler?

    A Get Column/Row would make the function less prone to breakage than a VLOOKUP/HLOOKUP which will help simplify the life of users. In my experience many users can figure out how to use a VLOOKUP but when it breaks they don't know how to diagnose it.

    The "Value if no Match" would preclude the need for nesting with an IFERROR but by the time a user understands the concepts well enough to utilise this optional parameter effectively, they could probably use the nested functions.

    Finally, only returning exact matches will suit 95% of scenarios but when the user needs a closest match they will have to go and learn VLOOKUP/HLOOKUP or INDEX/MATCH anyway and Excel will have added yet another way of looking things up from data. The very fact there are so many ways of doing it already, is a source of confusion for beginner/intermediate users. Will the benefit of this function be worth the addition of another function? idk.

  • Akkaz commented  ·   ·  Flag as inappropriate

    much needed.
    Just more thing to add, it should handle multiple criteria

  • Wyn Hopkins commented  ·   ·  Flag as inappropriate

    Wow, I’ve not been getting alerts on updates to this so apologies for my lack of response. I’ll double down on my efforts of promoting this now. P.S. thanks Mark and Greg

  • Simon commented  ·   ·  Flag as inappropriate

    Sorry I can't read 102 comments to see if this is already mentioned but my ideal vlookup would be:

    xlookup(what to find [string], where to find [range], column heading name to find in [string],
    Column header name to return [string], orientation [vertical or horizontal], find which [first - first match, n - nth match, No multi -error if multiple matches or return only one])

    [ ] are attributes for the variables required
    [string] would be a “string” or a cell reference that returns a string. In this context a string would be anything (strings, integers, numbers) as now.
    Column heading name - could be a string “name” or a reference to a excel table column (table1[Name]) as in this context that makes more sense than a reference to the header of that table column (table1[[#Headers],[Name]]). I guess it would also need to have a number offset, as now, to maintain compatibility and to help those that use a formula to derive the number (its March so offset by 3 type of logic)

    This lets you;
    - look in any column on a range (no more 1st column only)
    -Return any result from a column either before or after the searched one.
    -It is driven by column headings not column offset which means changing the table (adding columns, changing order) doesn’t break it (but changing column headings could)
    -orientation – allows it to be vlookup or hlookup – default would be vlookup
    -find which – defaults to first match but can return a specific one (the 3rd match) and more importantly can return an error when more than one result exists.

    Overall this should behave as if I had joined the two tables in (power) Query, once the join is made I can return any column I choose and it errors if I create a many:many or works with a 1:many.

    I already have something similar to this in vba and it works perfectly with Excel tables, which are fantastic.

  • SUNILSGAWDE commented  ·   ·  Flag as inappropriate

    VLOOKUP ALWAYS LOOKS UP ON RIGHT SIDE. ITSEAR DOES NOT CHECK FOR COLUMNS TO THE LEFT SIDE. WE NEED THIS FEATURE AS WELL. HOW ABOUT USING NEGATIVE COLUMN NO. FOR EXAMPLE,

    VLOOKUP(SEARCH CELL REF, AREA TO CHECK, -2, FALSE)

  • Mark commented  ·   ·  Flag as inappropriate

    The best tip about INDEX/MATCH actually came from Wyn Hopkins himself and in my opinion its genius.

    Set-up an autocorrect to replace "iii" with =INDEX( DblClk_to_Select_Column_to_return, MATCH( DblClk_Single_Lookup_Cell, DblClk_Lookup_Column, 0),0).
    Then all you need do is type "iii" in a cell and press enter. Now edit the equation that shows the above and use the names to remind you what reference to put where.

    The webpage where I found this is: http://www.contextures.com/newsletter/excelnews2016/20160503ctx.html

  • Excel Help commented  ·   ·  Flag as inappropriate

    Once I learned INDEX/MATCH, I never looked back. As most people point out, it is powerful and gets the job done. However... beginners usually learn VLOOKUP/HLOOKUP first and when I try to convert er...I mean, teach them INDEX/MATCH, the experience is harrowing because the argument order is reversed and the argument terminology is different. I've literally seen a coworker post a sticky on their well with both formulas written out and arrows describing how the arguments relate to each other. That's the kind of end user we're talking about here, the kind that is less technologically inclined than all of us here on uservoice.

    If MSFT or the community believes that keeping VLOOKUP is important, at least consider standardizing the order and wording of the function with its more powerful cousin.

  • Anonymous commented  ·   ·  Flag as inappropriate

    while you're about it, add a variable for getting the nth instance of a match too!!!

    =GETMATCH(ReferenceToMatch, MatchColumn/Row, GetColumn/Row, Optional Value if no match, optional instance of match)

    Thanks!

  • Col Delane commented  ·   ·  Flag as inappropriate

    Greg: We'll have to agree to disagree, as I will not hesitate to call out flawed reasoning or specious arguments, even on forums such as this.

    I've seen far too many so-called expert/gurus/bloggers who write about what they perceive as "problems" with VLOOKUP or "disadvantages" when unfairly comparing it to an INDEX/MATCH combination (e.g. can't look left, easily corruptible when inserting columns, etc.) - when their claims just don't stack up. VLOOKUP can look left when nested with CHOOSE, and can handle inserted columns when nested with any one of three functions.

    I don't believe VLOOKUP is difficult to comprehend - after SUM it was one of the first functions I learned to use. If a user can only use single function formulas then they haven't progressed very far.

  • Greg commented  ·   ·  Flag as inappropriate

    Hi Col,

    Wyn can write nested functions with his eyes closed. But this isn't about him or you or me or any expert/guru/MVP. It's about the END USER. It looks like in his experience, dealing with thousands of end users, they struggle with nested functions, especially if they don't use them often. By making this suggestion he is trying to make the life of the end user simpler, and thereby maybe more effective or less error prone. It's these incremental step changes that continue to make Excel better.

    Please do not criticize contributors or their suggestions in this forum. By all means critically assess their proposal and if you disagree then state why and propose a better solution. In short: if you have nothing constructive to say, say nothing.

← Previous 1 3 4 5 6

Feedback and Knowledge Base