Look up one value and return multiple corresponding values

[Sandeep Jangra]

D'all,
I have a two column (Name and Amount) list with repeated values, Plz help me
about these ...
1. I want to look up all the repeated values against a single name
2. lookup heightest and lowest amount against a single name.

Sandeep Jangra

 

[muddan madhu]

try this

Names are in Col A and Amounts are in Col B

using advance filter take out unique names in Col A paste it in Col C

C2 you have names in D2 put this formula and drag it down

=IF(ISERROR((INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=$C$2,ROW($A$1:$A
$100)),ROW(1:1)),0))),"",INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=$C
$2,ROW($A$1:$A$100)),ROW(1:1)),0))

Use Ctrl + shift + enter ( array formula )

To know the highest and lowest amount use this forumla

In Cell F2 put this formula to know the Highest value
=MAX(IF(--(A2:A100=C2)*(B2:B100)=0,"",(A2:A100=C2)*(B2:B100))) ( use
ctrl + shift + enter once u enter the formula )

To know the lowest value use this formula
=MIN(IF(--(A2:A100=C2)*(B2:B100)=0,"",(A2:A100=C2)*(B2:B100))) ( use
ctrl + shift + enter once u enter the formula )

 

[Bob Phillips]

I think that this is array formula is all that you need to get the name that
is in C2

=IF(ISERROR(SMALL(IF($A$1:$A$100=$C$2,ROW($A$1:$A$100),""),ROW(A1))),"",
INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=$C$2,ROW($A$1:$A$100),""),ROW(A1)),0))

 

[Max]

a. Think Autofilter will be the easiest approach ...

Select entire cols A & B, then click Data>Filter>Autofilter
1. Click on the droplist in A1, select any name

You get all the filtered rows for that single name

2. Click on the droplist in B1, Choose "Top 10"

You get a dialog box to further tinker with for that single name

To quickly clear all filterings for the next name,
just click Data>Filter>Show All

b. Another easy, fast & relevant summarization to do is to create a pivot
table

Here's the easy steps to lead you in (in excel 2003):
Select any cell within the source table
Click Data > Pivot table. Click Next>Next
In step 3 of the wizard, click Layout, then:
Drag n drop "Name" into ROW area
Drag n drop "Amount" into DATA area 3 times, one below the other
Leave the 1st as Sum,
Double-click on the 2nd>select Max under Summarize by > OK
Double-click on the 3rd>select Min under Summarize by > OK
Click OK > Finish

Hop over to the pivot sheet (new sheet to the left). Drag "Data" n drop it
over "Total" to get a nice summary table showing all the unique names in the
1st col, with their corresponding Sum, Max & Min amounts in the 3 adjacent
cols to the right
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---

 

[Sandeep Jangra]

Dear Max,
I have to update that file on daily basis. Is it possible to do all these
steps in one shortcut formula?

 

[Max]

I'm sorry to have wasted your time trying to interest you in those 2 easy
options. You have been given formulas by other responders in your posting
here. You can take it up further with them.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik