Lookup / Summary Table

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

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 don’t want is:

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

In other words, I don’t want a bunch of blank rows in the new summary table.
What function(s) can I use to create my summary table?
 
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.
 
Normally a pivot table would work. I didn't mention in my original post that
the data that I need to summarize will be imported into Microsoft Streets &
Trips. The example I gave was an oversimplification of what I'm doing. I
really have a list of locations and their corresponding longitude / latitudes
that Streets & Trips will display on a map. The import feature is not very
fancy and can not handle things like filters / pivot tables. Thanks for the
suggestion though, it would work if I didn't have to use the data for
importing.
 
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
 
Ok here goes...

(Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously you
can change that reference to whatever you need.)

In Sheet2!A1

=IF($Z$1="","-",$Z$1)


in Sheet2!A2

=IF($A1="-","-",
IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-",
INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))


in Sheet2!B1:B2

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


Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for other
purposes, as far across as required).

I suspect these could be simplified further, especially if that second one
was re-created as an array formula (which I prefer to avoid where possible),
but my eyes are starting to itch, so I'm off to bed. Night!

HTH
Steve D.
 
Slight amendments: the first MATCH in the second formula should have ,0 at
the end, and using SMALL rather than LARGE (with slight change to the final
argument) may make the third formula a little more readable.

=IF($A1="-","-",
IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
IF(MATCH($A1,$Z$1:$Z$10,0)=COUNTA($Z$1:$Z$10),"-",
INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))

=IF($A1="-","-",
INDEX(Sheet1!B$1:B$50,SMALL(INDEX((Sheet1!$A$1:$A$50=$A1)*
(ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
COUNTIF(Sheet1!$A$1:$A$50,"<>"&$A1)+COUNTIF($A$1:$A1,$A1))))
 
Worked like a charm. Thanks for all your help!

Steve Dunn said:
Slight amendments: the first MATCH in the second formula should have ,0 at
the end, and using SMALL rather than LARGE (with slight change to the final
argument) may make the third formula a little more readable.

=IF($A1="-","-",
IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)>COUNTIF($A$1:$A1,$A1),$A1,
IF(MATCH($A1,$Z$1:$Z$10,0)=COUNTA($Z$1:$Z$10),"-",
INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1))))

=IF($A1="-","-",
INDEX(Sheet1!B$1:B$50,SMALL(INDEX((Sheet1!$A$1:$A$50=$A1)*
(ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),),
COUNTIF(Sheet1!$A$1:$A$50,"<>"&$A1)+COUNTIF($A$1:$A1,$A1))))
Click to expand...
 
Back
Top