Return Multiple Items with One Lookup Value

Question

Level 1

9 points

Return Multiple Items with One Lookup Value

Hi there,

Within my data set there are multiple values that I would like to return into a different table.

For example:

In the "players from australia" table I would like to search the main data set for the "AUS" tag in column B and then automatically display the names of those players.

There was something similar posted a few days ago Search sheet and return associated values for multiple matches and I tried to apply those formulas to my data but it didn't work...perhaps slightly different application of the function.

fyi: the "category" table is simply to add up the number of players from each country and return a numerical value, as opposed to the names. I also use it as the cell from which to determine which value to search and display, i.e. A2 for the AUS players to be displayed in the empty table.

Any help would be greatly appreciated!!

Ed.

MacBook Pro with Retina display, OS X Yosemite (10.10.2)

Posted on Mar 1, 2015 9:03 AM

Reply

Answer 1

SGIII

Level 8

37,592 points

Mar 1, 2015 9:26 AM in response to AussieEd1

There are ways to do this with an extra helper "index" column in the main data table.

But first, have you considered what may be the easiest solution. In the data table, filter on AUS (or whatever), select the body cells, command-c to copy, click once in the destination table and command-v to paste?

SG

Reply

Answer 2

AussieEd1 Author

Level 1

9 points

Mar 1, 2015 9:37 AM in response to SGIII

Thanks for your time and you reply.

Yes filtering with copy+paste is indeed an easy solution, but with large sets of data (and also frequently changing data) I would like to try and get it done with a formula. I know it is a little hard to step into my brain and foresee all the uses of this function!

Regards,

Ed.

Reply

Answer 3

SGIII

Level 8

37,592 points

Mar 1, 2015 10:17 AM in response to AussieEd1

You could try something like this:

The formula in the Index column, copied down:

=B2&COUNTIF(B$1:B2,B2)

The formula in A2 of the Players From table, copied down and right:

=INDEX(Table 1::$A,MATCH(A$1&ROW()−1,Table 1::$D,0),)

To suppress the display of the red warning triangles, you can wrap that in IFERROR, like this:

=IFERROR(INDEX(Table 1::$A,MATCH(A$1&ROW()−1,Table 1::$D,0),),"")

This takes the value in row 1, appends the row number it's on less 1, and uses MATCH to find what row contains that value in the Index column of the other table. That row number is fed to INDEX, which returns the value from column A.

SG

Reply

Answer 4

AussieEd1 Author

Level 1

9 points

Mar 1, 2015 10:50 AM in response to SGIII

Yeh good one. That has worked as intended and immediately helped me with other formulas.

Cheers.

Reply