+ Reply to Thread
Results 1 to 4 of 4

Help for VLOOKUP, OFFSET, and MATCH to search for a reference and return the needed value

  1. #1
    Registered User
    Join Date
    12-09-2020
    Location
    New Jersey
    MS-Off Ver
    Excel for Mac Version 16.43
    Posts
    2

    Help for VLOOKUP, OFFSET, and MATCH to search for a reference and return the needed value

    Hello,

    I have been struggling to find an formula that will prevent me from having to manually input the table array using VLOOKUP.
    In the past my formula was

    =IFERROR(VLOOKUP(A3,Games!$A$5:$C$16,2,0),IFERROR(VLOOKUP(A3,Games!$D$5:$F$16,2,0),"N/a"))
    Screen Shot 2020-12-09 at 3.57.24 PM.png
    And I would be grabbing the data from here
    Screen Shot 2020-12-09 at 3.59.29 PM.png

    Rather than having to individually do this formula for every game played I was hoping there was a formula I can use to just drag it across the sheet for every game 1-100.

    I tried doing
    =IFERROR(VLOOKUP(A3,OFFSET(MATCH(E2,Games!$A$3:$F$49,0),2,0,12,3),2,0),IFERROR(VLOOKUP(A3,OFFSET(MATCH(E2,Games!$A$3:$F$49,0),2,3,12,3),2,0),"N/a"))

    My thoughts were that this would pick Game 1, find it in the array, and then perform the offset function for the table underneath it, and then follow up by performing the Vlookup I need to grab the value for the chosen player, but the formula gives me an error so any suggestions would be great, thanks!
    Last edited by rksmith97; 12-09-2020 at 05:58 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Help for VLOOKUP, OFFSET, and MATCH to search for a reference and return the needed va

    Can you upload a workbook with sample data rather than an image?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    12-09-2020
    Location
    New Jersey
    MS-Off Ver
    Excel for Mac Version 16.43
    Posts
    2

    Re: Help for VLOOKUP, OFFSET, and MATCH to search for a reference and return the needed va

    Hi I believe this should be good, thank you!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Help for VLOOKUP, OFFSET, and MATCH to search for a reference and return the needed va

    Referring to the worksheet in the topmost screen snippet as summary, you seem to want a formula which searches for names in summary!A3:A999 in winning and losing team columns for each game. If the games ranges shown in the bottom 2 screen snippets were in the same set of rows, just stacked vertically due to text box width limitations, so the Game 1 label in Games!A1, the Games 2 label in Games!G1, and further labels ever 6th column, try

    summary!B3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. [SOLVED] Help needed combining SUM OFFSET MATCH with condition
    By cuddy89 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2020, 12:24 PM
  2. Replies: 0
    Last Post: 09-08-2017, 09:31 AM
  3. Replies: 9
    Last Post: 09-05-2017, 11:35 AM
  4. [SOLVED] VBA help needed search a text string then clear offset column values
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2015, 03:51 AM
  5. Replies: 7
    Last Post: 07-17-2011, 09:49 AM
  6. Replies: 1
    Last Post: 07-13-2011, 09:22 AM
  7. Limitations when Combining OFFSET and MATCH to return a cell reference
    By dangermousedale in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2008, 08:39 PM

Tags for this Thread

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