listing details

Hi All

I have a table that contains (sheet 1) 2 columns A column contains Team Name, B column contains Staff Name. I am trying to list the Staff Names down column A of a new sheet (Sheet 2) if they = Sheet 2 b1

I assume Sheet2, Column B contains team names, to which you want to match
the staff name from the first sheet.

Try this in A2 of Sheet2, and copy down as needed:

=IF(ISNA(MATCH(B2,Sheet1!$A$2:$A$20,0)),"No
Match",VLOOKUP(B2,Sheet1!$A$2:$B$20,2,0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"mathewheys" <mathewheys.1s6paa_1121385907.8906@excelforum-nospam.com> wrote
in message news:mathewheys.1s6paa_1121385907.8906@excelforum-nospam.com...
>
> Hi All
>
> I have a table that contains (sheet 1) 2 columns A column contains Team
> Name, B column contains Staff Name. I am trying to list the Staff Names
> down column A of a new sheet (Sheet 2) if they = Sheet 2 b1
>
>
> --
> mathewheys
> ------------------------------------------------------------------------
> mathewheys's Profile:
http://www.excelforum.com/member.php...o&userid=25093
> View this thread: http://www.excelforum.com/showthread...hreadid=387374
>

On sheet2 at b1:

=vlookup(b2,sheet1!a2:b100,2,false)
and then on b2, enter your "team name" to have vlookup() get the "staff
name".

Danny



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Danny there is more than one staff member in a team this only brings back the first record every time

Originally Posted by Danny

On sheet2 at b1:

=vlookup(b2,sheet1!a2:b100,2,false)
and then on b2, enter your "team name" to have vlookup() get the "staff
name".

Danny



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Try this *array* formula in Column A of Sheet2:

=INDEX(Sheet1!$B$1:$B$30,SMALL(IF(Sheet1!$A$1:$A$30=$B$1,ROW($A$1:$A$30)),RO
W(A1)))
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Copy down as far as you think that there might be names to find.
This formula returns a #NUM! error when it runs out of names to match.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"mathewheys" <mathewheys.1s70eh_1121400325.3428@excelforum-nospam.com> wrote
in message news:mathewheys.1s70eh_1121400325.3428@excelforum-nospam.com...

Danny there is more than one staff member in a team this only brings
back the first record every time
Danny Wrote:
> On sheet2 at b1:
>
> =vlookup(b2,sheet1!a2:b100,2,false)
> and then on b2, enter your "team name" to have vlookup() get the
> "staff
> name".
>
> Danny
>
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/


--
mathewheys
------------------------------------------------------------------------
mathewheys's Profile:
http://www.excelforum.com/member.php...o&userid=25093
View this thread: http://www.excelforum.com/showthread...hreadid=387374