Look up one value and return multiple corresponding values
 

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

Look up one value and return multiple corresponding values
 

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$10 0=$C$2,ROW($A$1:$A
$100)),ROW(1:1)),0))),"",INDEX($B$1:$B$100,SMALL(I F($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 )











On Oct 15, 12:55*pm, Sandeep Jangra
wrote:
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

Look up one value and return multiple corresponding values
 

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))

--
__________________________________
HTH

Bob

"Sandeep Jangra" wrote in message
...
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

Look up one value and return multiple corresponding values
 

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

Select entire cols A & B, then click DataFilterAutofilter
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 DataFilterShow 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 NextNext
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 2ndselect Max under Summarize by OK
Double-click on the 3rdselect 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" wrote:
I have a two column (Name and Amount) list with repeated values,
1. I want to look up all the repeated values against a single name
2. lookup heightest and lowest amount against a single name.

Look up one value and return multiple corresponding values
 

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

"Max" wrote:

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

Select entire cols A & B, then click DataFilterAutofilter
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 DataFilterShow 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 NextNext
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 2ndselect Max under Summarize by OK
Double-click on the 3rdselect 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" wrote:
I have a two column (Name and Amount) list with repeated values,
1. I want to look up all the repeated values against a single name
2. lookup heightest and lowest amount against a single name.

Look up one value and return multiple corresponding values
 

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
---
"Sandeep Jangra" wrote in message
...
Dear Max,
I have to update that file on daily basis. Is it possible to do all these
steps in one shortcut formula?