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

1. 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!

2. 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?

3. 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!

4. 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:
`Please Login or Register  to view this content.`

Users Browsing this Thread

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

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