Excel Lookup Question

[ccepaulb]

Is there a way to have Excel use a lookup function to return multiple rows based on a selection. For example vlookup will pull information based on a one record match. I would like for Excel to return multiple records (without using a pivottable, won't work for this).
(example)
Retailer Outlet 2004 Sales etc… etc…
1 Jim's Corner Store $500
1 Jim's Corner Market $1,500
1 Jim's Gas $250
2 Bob's Convenience $5,000
2 Bob's Conv #2 $8,521
3 Fast Fuel #1 $1,025
4 Quick Trip #1 $6,000
4 Quick Trip #2 $7,500
4 Quick Trip #3 $2,400
4 Quick Trip #4 $35,264
4 Quick Trip #5 $63,541
4 Quick Trip #6 $6,874
4 Quick Trip #7 $321
4 Quick Trip #8 $687
4 Quick Trip #9 $21,321
4 Quick Trip #10 $6,984
5 Fred's Amoco $542

Now can I have a lookup find all records for retailer 4 and have it show only those (Tried a filter function, won't work, I have a certain format that it needs to come down in) Any help would be appreciated

Thanks, Paul

 

[SkipVought]

Paul,

Look like you might be trying to make an Invoice application???

Several Suggestions.

1) NORMALIZE your data -- for instance
[tt]
Retailer Outlet Year Sales
1 Jim's Corner Store 2004 500
1 Jim's Corner Market 2004 1500
1 Jim's Gas 2004 250
2 Bob's Convenience 2004 5000
2 Bob's Conv #2 2004 8521
3 Fast Fuel #1 2004 1025
4 Quick Trip #1 2004 6000
4 Quick Trip #2 2004 7500
4 Quick Trip #3 2004 2400
4 Quick Trip #4 2004 35264
4 Quick Trip #5 2004 63541
4 Quick Trip #6 2004 6874
4 Quick Trip #7 2004 321
4 Quick Trip #8 2004 687
4 Quick Trip #9 2004 21321
4 Quick Trip #10 2004 6984
5 Fred's Amoco 2004 542
[/tt]
2) Advance Filter COLUMN A to get a list of UNIQUE VALUES. Use this list as the range for a DropDown.

Using the result of the selection from the Drop Down, use MS Query (Data/External Data/New Query...) parameter query to return the rows for that selection. If you set up a criteria [What Retailer?], you can LINK to a cell and run the query any time that value changes, returning the rows for that selection. VOLA!

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[ccepaulb]

I tried MS Query, but struggled with assigning the criteria. How would I link it to a cell or drop down control?
I also was confused as to how to update the query, it seemed a little different than Access

Thanks, Paul