Finding Names of a group...

[MickeyPvX]

<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=165 border=0><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=42 height=17> Job1
</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=68> Name</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 41pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=55> Info</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=42 height=17> Job3</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=68> Name</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 41pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=55> Info</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=42 height=17> Job2</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=68> Name</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 41pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=55> Info</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=42 height=17> Job1</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=68> </TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 41pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=55> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 32pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=42 height=17> Job3</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 51pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=68> </TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 41pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=55> </TD></TR></TBODY></TABLE>

So I have this type of a sheet, with a bunch of different job titles on it, and I want to extract the Name and other Info from all the people that have a particular job title. As shown here, the job titles are not in any sort of order, and I'd like to keep them that way for other reasons. Finding the first entry is easy:

=VLOOKUP("Job1",$B$7:$L$46,2,FALSE) (puts the Name in that cell)

It's getting the rest of them that I'm having problems with.

 

[tweedle]

if this is a one-time thing, would data/filter, copy/paste not work?

if this is not a one-time thing, where did you want the results to be and should the solution be just formulas or is vba permissible?

and what version of XL r u on?

 

[MickeyPvX]

I think I got it to do what I wanted, and in regards to your question, it would be more than a one time thing. Basically I wanted to be able to input a bunch of people's info on one side, then display the specific people I wanted on that list on a different set of cells on the same sheet. Right now I have it set up with the list of people in B7:L46, and the extracted data starting in column N:

Cell O3:
=VLOOKUP("Job1",$B$7:$L$46,2,FALSE)

for the first name, and in the cell below it (assuming the job title I'm looking for is in cell N3):

Cell O4:
=VLOOKUP($N$3,INDEX($B$7:$L$46,MATCH($O3,$C$7:$C$46,0)+1,1):$L$46,2,FALSE)

So all I have it doing is finding the cell where the last name was found, moving down one row, and searching the array from that new cell through L46. Seems to work pretty well so far, but the #N/A errors are somewhat of an eyesore when it stops finding the people I'm looking for.

I'm running Excel 2007

 

[tweedle]

Great.

The anti-N/A
=IFERROR({Your Formula},"")