Ok, I have a speadsheet called 'Specs' with over 3000 rows of information.
Starting with a customer site location, the rows contain specific information
about each customer. In many instances there are multiple site entries for
any givern customer due to there being multiple items. I can use autofilter,
but it does not do all that I need to accomplish. What I want is to be able
to perform a lookup on another spreadsheet called 'Search' (within the same
worksheet) by inputting the site name. Using vlookup I am able to retrieve
the first instance of the site and the pertinent info available to that site.
I want to know if it is possible to make it display all the instances of the
given site name, or how to make it perform a lookup and display the
information that is on the row below the site I perform the lookup on. Using
vlookup and offset together gives me an error. The column headers a

SITE UNIT# SERIAL# MODEL# PART1 PART2 PART3 ...etc.

Site A 1
Site A 2
Site A 3
Site B 1
Site C 1
Site C 2
Ex: if I do a lookup on Site A I want to see a list of all 3 Site A's, as an
alternate solution I can work with getting the info on the site directly
below the site I do a lookup on, such as vlookup on SITE B, and getting Site
C (1) to display.

Thanks!

Squeaky

Assuming that A2:G7 contains your data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

J2, copied down and across:

=IF(ROWS(J$2:J2)<=COUNTIF($A$2:$A$7,$I$2),INDEX(A$ 2:A$7,SMALL(IF($A$2:$A$
7=$I$2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(J$2:J2))),"")

....where I2 contains the criteria, such as 'Site A'.

Hope this helps!

In article ,
Squeaky wrote:

Ok, I have a speadsheet called 'Specs' with over 3000 rows of information.
Starting with a customer site location, the rows contain specific information
about each customer. In many instances there are multiple site entries for
any givern customer due to there being multiple items. I can use autofilter,
but it does not do all that I need to accomplish. What I want is to be able
to perform a lookup on another spreadsheet called 'Search' (within the same
worksheet) by inputting the site name. Using vlookup I am able to retrieve
the first instance of the site and the pertinent info available to that site.
I want to know if it is possible to make it display all the instances of the
given site name, or how to make it perform a lookup and display the
information that is on the row below the site I perform the lookup on. Using
vlookup and offset together gives me an error. The column headers a

SITE UNIT# SERIAL# MODEL# PART1 PART2 PART3 ...etc.

Site A 1
Site A 2
Site A 3
Site B 1
Site C 1
Site C 2
Ex: if I do a lookup on Site A I want to see a list of all 3 Site A's, as an
alternate solution I can work with getting the info on the site directly
below the site I do a lookup on, such as vlookup on SITE B, and getting Site
C (1) to display.

Thanks!

Squeaky