Compiling list using VLOOKUP?

Hey guys,

After endless searching I'll give it a shot here. What I have is an exported player file from a simulation basketball program. Each player is given a unique playerID ranging from 1:740. Each row contains a playerID and relevant data for that player.

The task I'm trying to complete involves the player's teamID value. As up to 15 players will have the same teamID value (ranging 1:30), I'm trying to use a function to list all instances a particular teamID appears.

Example: teamID=1
I want to return a list of all 15 players which have this teamID.

Using the VLOOKUP, I am able to return the first occurence. After some searching, I've come across suggestions of using the OFFSET, MATCH and INDEX functions, but I haven't had much luck.

I have attached a trimmed-down version of the file I am working with. The data is on the 'Player' sheet, and I will be calling the info on Sheet1.

Any help will be greatly appreciated!

For the first name, try...

B2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(B$2:B2)<=COUNTIF(Players!$B$2:$B$741,Sheet1!$A$2),INDEX(Players!$D$2:$D$741,SMALL(IF(Players!$B$2:$B$741=Sheet1!$A$2,ROW(Players!$B$2:$B$741)-ROW(Players!$B$2)+1),ROWS(B$2:B2))),"")

For the last name, try...

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(B$2:B2)<=COUNTIF(Players!$B$2:$B$741,Sheet1!$A$2),INDEX(Players!$E$2:$E$741,SMALL(IF(Players!$B$2:$B$741=Sheet1!$A$2,ROW(Players!$B$2:$B$741)-ROW(Players!$B$2)+1),ROWS(B$2:B2))),"")

Note that I replaced...

Players!$D$2:$D$741

with

Players!$E$2:$E$741

Change the formula, accordingly, for the other columns.

Thanks for the quick reply. It didn't seem to work for me, but I'm not sure if it's got to do with a box asking me to Update Values appearing when I hit CTRL + SHIFT + ENTER...

Here's a sample file...

You're a legend! Thanks for the help.

Hi Domenic,

In your reply Post #2 and #4, I am in need of a similar table lookup. Using the example in this post, how can I modify the formula to automatically scan the column header to find the appropriate column instead of hardcoding. That is, the portion of INDEX(Players!$E$2:$E$741,SMALL(IF(Players!$B$2:$B$741... match the header of Sheet 1 to the larger table in Players. This way I can customize the data columns using drop downs without having to know which column the original data resides. I tried using INDEX(Players!$A1:$CD1,MATCH(C$1,0,0,0),SMALL(.... But it hasn't worked out.

Thank you,


David

Tried your suggestion and it worked perfectly! Thank you. Would have posted sooner but was away on vacation. To get rid of the pesky #NUM errors, I wrapped the entire formula within an ISERROR function. Wish there was a prettier/less cumbersome way of doing this though (I'm using Excel 2003). I even made the Table with conditional format to create table lines if there is data in each row and column. Now when I add any column header via drop down validation list, the header is automatically shaded and the data pops into place with table lines.

Again thanks for your help.

Assuming that the formula is entered in B2 and copied down, try...

B2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

Please Login or Register  to view this content.