+ Reply to Thread
Results 1 to 6 of 6

Upgrade from VLOOKUP to INDEX & MATCH

Hybrid View

  1. #1
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    1,003

    Upgrade from VLOOKUP to INDEX & MATCH

    Hi All, In the attached workbook, on the sheet entitled RESULTS, I use VLOOKUP to sort out the result after a golf competition using several criteria. It works fine, just have to keep changing the tables size when new players are added or deleted. I keep reading articles saying that using INDEX and MATCH is a superior way of doing things and I believe I could then take the data straight from the Master sheet without needing a table, but I don't know how or where to start. If somebody has a few minutes to take a look, I'd be grateful. Your advice maybe to stick with what I've got and I'm okay with that but I'm always looking to improve/modernise things.
    Many Thanks.
    Attached Files Attached Files
    Last edited by Marvo; 08-27-2022 at 04:45 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Upgrade from VLOOKUP to INDEX & MATCH

    I use INDEX/MATCH a lot, but also VLOOKUP, and in your case I would suggest that you stick with VLOOKUP, although you could improve it slightly. For example, in B14 you have this:

    =VLOOKUP($V14,data,3,0)

    and this is copied across the next few columns with only the 3 changing (in turn to 4, 5, 6, 7 and 8). You could use:

    COLUMNS($A:B)+1

    instead of the 3, so that when you copy across the term will return the numbers that you want (therefore, you are only using one formula).

    I've not really looked at other parts of your file.

    Hope this helps.

    Pete

  3. #3
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    1,003
    Thanks for the advice, Peter, I'll make those changes you suggest.
    Last edited by Marvo; 08-25-2022 at 04:54 PM.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Upgrade from VLOOKUP to INDEX & MATCH

    I would use this helper

    U3
    =MATCH(LARGE($W$3:$W$66,ROWS(U$3:U3)),$W$3:$W$66,)

    Then B3:G3
    =INDEX(Y$3:Y$66,$U3)

    or With MS365

    =SORTBY(Y3:AD66,W3:W66,-1)
    Attached Files Attached Files

  5. #5
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    1,003

    Re: Upgrade from VLOOKUP to INDEX & MATCH

    Sorry Bo_Ry, I didn't get notification of your post, only just seen it, I'll take a look. Many thanks.

  6. #6
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    1,003

    Re: Upgrade from VLOOKUP to INDEX & MATCH

    Had a chance to work on this Bo_Ry and many, many thanks, I've made a lot of changes based on your example. It's a bit of trial and error but everything is still working as it should so.... Super!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA function to match multiple criteria faster than vlookup or index match
    By bkav1991 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-09-2020, 09:14 AM
  2. Replies: 1
    Last Post: 08-22-2020, 04:47 PM
  3. VLOOKUP or INDEX/MATCH with multiple column index numbers
    By cerebral87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2017, 07:13 PM
  4. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  5. Replies: 1
    Last Post: 06-18-2015, 08:45 AM
  6. Replies: 3
    Last Post: 05-19-2014, 02:01 PM
  7. Replies: 13
    Last Post: 12-13-2012, 11:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1