Lookup / Summary Table
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?
|