INDEX function

[John Case]

I am trying to make a query formula that allows my employees to type in their
name and read their stats from several other files. I can't use VLOOKUP
because there are usually more than one line of data per person. See Example:

I want a function that will search through this list:

Jeff Corwin New York 3
April Bellingham Chicago 5
Jeff Corwin Chicago 4
Andrew Mulligan Dallas 6
Jeff Corwin Dallas 6

And return this data (if value entered in search cell is "Jeff Corwin"):

Jeff Corwin New York 3
Jeff Corwin Chicago 4
Jeff Corwin Dallas 6

 

[Max]

Here's one simple formulas driven way to get it up & going ...

Illustrated in this sample:
http://www.freefilehosting.net/download/3jidc
Query multiple results in other sht.xls

Source data in x, in cols A to C,
data from row2 down (key col's names in A2 down)

In another "search" sheet,
Input in A2, eg: Jeff Corwin

In C2:
=IF($A$2="","",IF($A$2=x!A2,ROW(),""))
Leave C1 blank

In D2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(x!A:A,SMALL($C:$C,ROWS($1:1))))
Copy D2 to F2. Select C2:F2, copy down to cover the max expected extent of
data in x. Minimize/Hide col C. Cols D to F returns the multiple results for
the name entered in A2, all neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik

 

[John Case]

Thanks! I know this was a while back but I was eventually able to get this to
work.

 

[Max]

Glad to hear you got it going, John
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik