Need Vlookup or Index Match to return multiple values

**harrydnyc** · 07-27-2016, 06:05 PM

Hello - I am trying to build a spreadsheet for my fantasy football league that tracks all picks in the draft. I have a tab where all the picks are listed, and then I am trying to pull the results into another sheet which lists all the players by the person that selected that player. Since the order that players can select a player is random (auction draft), I am unable to simply pull the results into the sheet by the order of which the players were selected. I also am unable to use a Vlookup since each player will select a total of 15 players in the draft. I believe there is way to use Index and Match to pull multiple values out of an array, but I haven't been able to figure it out. Can someone please help. Here is a link to the file: https://www.dropbox.com/s/mtg13lzvs3...raft.xlsx?dl=0

Basically, I am trying to pull data from the sheet titled "Picks" and pull it into the sheet titled "Team Board." In row 1 of the Team Board sheet, you can see the name of the individual that I need to pull in the picks for. Each individual will select 15 players in total in the draft, so I need a formula that will pull all 15 players that were entered into the Picks tab into the Team Board tab. As an FYI, I have not made the total 180 picks in the "Picks'" tab yet, so the formula should not pull in all 180 (15 players * 12 individuals) at this point.

Finally, I wouldn't be able to use VBA since this ultimately needs to be on Google Sheets.

Any help would be appreciated!

Thanks,

Harry

**FlameRetired** · 07-27-2016, 07:24 PM

Hi Harry,

Try array entering this formula in A3 of 'Team Board' and filling down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then in B3 and filled down this non array formula.

Formula:

Please Login or Register  to view this content.

After that simply copy A3:B17 and paste into C3, E3, G3 ... W3.

**harrydnyc** · 07-28-2016, 10:10 AM

Worked perfectly. Thanks so much. If you have time and are willing, I would love to understand the logic behind the Array formula you provided.

Thanks again!

Harry

**FlameRetired** · 07-28-2016, 05:52 PM

I'll do my best.

The heart of this is the MATCH nested inside the IF function. If we applied only the MATCH it would return several numbers along with #N/A errors. SMALL does not

ignore errors. Nested inside IF means the MATCH is conditional upon A$1=Picks!$D$2:$D$181 being TRUE. In that case MATCH would return several numbers along

with FALSE. This is good because SMALL will ignore text and group those numbers in order according to ROWS($3:3) as you fill down. Those numbers serve

as indexing numbers passed to INDEX telling it to return data from each of those rows in Picks!$B$2:$B$181 corresponding to the index numbers returned by

MATCH.

If you are not familiar with it there is a feature called 'Evaluate Formula' in Excel. Here's how to use it.

Click on one of the cells containing the formula, click Formulas in the ribbon and click Evaluate Formula. Click on the Evaluate

button. With each click Evaluate Formula will show you how the formula works step by step.

As an additional method select all of MATCH in the formula bar and press the F9 function key. The formula bar will display the resulting array. Hit escape and now

select all of the IF function including all of its arguments and press F9 again. You will see the difference. The F9 is slower but allows you to selectively examine the

parts (though out of context).

These two devices are of great help in dissecting formulas and self instruction. I encourage use of them whenever I can.

Has this helped?

Need Vlookup or Index Match to return multiple values

LinkBack

Thread Tools

Rate This Thread

Display

Need Vlookup or Index Match to return multiple values

Re: Need Vlookup or Index Match to return multiple values

Re: Need Vlookup or Index Match to return multiple values

Re: Need Vlookup or Index Match to return multiple values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Match Index to return multiple values

[SOLVED] Vlookup/index/match to return all values that match

Using Index/Match to return multiple values for one match

VLOOKUP/INDEX/MATCH to return all values that match

[SOLVED] Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column

[SOLVED] How to use Index Match to return multiple values

Index Match or Vlookup to return values?

Bookmarks

Bookmarks

Posting Permissions