Matching multiple columns based on vlookup

[golffor1]

So I have done a lot of googling and I am almost done with this sheet. I am slowly learning excel and everything is going good until I want to learn something new.

So I was wondering if it was possible to find all of a teams players based on the team name?

I have the team name-(aka alliance) on one sheet...there maybe 1 person in that alliance or up to 99. I was wondering how would I find the people in that alliance?

I have the following code.

=INDEX('All Coords'!B1:B600, SMALL(IF($B$10='All Coords'!A1:A600, ROW('All Coords'!A1:A600)-ROW('All Coords'!A1)+1), ROW(1:1)))

I get the value of 0.

If it matters this is on the Dives Bombers alliance.

[Richard Buttrey]

Hi,

Check out the standard Advanced Data Filter functionality, or probably better still use a Pivot Table - see attached.

[golffor1]

Thank you for the quick reply. The only catch is that I would like to keep it as a formula and the reason for that is because I want to eventually transfer it to a google document. Is it possible to do this with a formula?

[Moo the Dog]

You can do it with an ARRAY formula, which is applied by pressing Ctrl + Shift + Enter, instead of just Enter, after typing the formula in the formula bar.

See the "PLAYER LIST" sheet of the modified workbook I attached.

Since you are using Excel 2003, I changed the formula to the following:

Please Login or Register  to view this content.

It is filled down then from A2:A100.

- Moo

[golffor1]

Sorry for the late reply....and I am not sure if this needs to go to another post.

My question I have on this is how do you get it to display the rest of the information? So for instance the coords

[Moo the Dog]

If you copy the column headings ('X cord' thru 'Total Taken') from the All Coords sheet to the PLAYER LIST sheet (paste into B1:L1), then paste this formula into cell B2 and fill right, then select B2:L2 and fill down to row 100, where the other formula in column A stops, it should return the data for you:

Please Login or Register  to view this content.

See my revised attachment.

- Moo

* One thing to note... there are duplicates in your list from the All Coords sheet (as evidenced by the duplicate names in the listing for the Fluffy Bunnies alliance. The VLOOKUP will only pull the FIRST row of data that it finds for any given name in the list. If the same name appears in the list twice, it will show the same values as the one prior... even if the data is different for both entires on the All Coords sheet.

Hope that makes sense.