Steve,

Normally AutoFilter would work. The example I gave was an
oversimplification of what I am doing. The summary table I am trying to
create will be imported by Microsoft Streets & Trips. The data I am working
with is actually a list of locations with their respective longitude /
latitude. Streets & Trips can't handle filters and pivot tables. Therefore
there is a need to create a new separate table to use for the import process.
To add a few questions to your original answer (which worked by the way,
thanks)...

1. How do I show more columns in my new summary table?
2. How do I add a second value for Excel to lookup?

Once again, thanks for your help.

Joe

"Steve Dunn" wrote:

Hi Joe,

have you tried using AutoFilter?

If you particularly need the results in a seperate table, follow this.
Assuming your lookup table is in Sheet1 and your results table will be in
Sheet2:

Put your lookup value in Sheet2!A1, then
in Sheet2!B1:

=IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(1:1),
INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$ A$50=$A$1)*
(ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),),
COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-")

in Sheet2!A2:

=IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(2:2),$A$1 ,"-")

Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required.



"Joe" wrote in message
...
I want Excel to lookup a value in a table and return each row it finds that
value in.

As an example:

Lookup Value: Ford

Lookup Table:

A1: Ford B1: Focus
A2: Chevy B2: Malibu
A3: Chevy B3: S10
A4: Ford B4: Ranger

Results Table:

A1: Ford B1: Focus
A2: Ford B2: Ranger

What I dont want is:

A1: Ford B1: Focus
A2: B2:
A3: B3:
A4: Ford B4: Ranger

In other words, I dont want a bunch of blank rows in the new summary
table.
What function(s) can I use to create my summary table?