Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
look up multiple values, to return only one value | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
Multiple conditions and multiple return values | Excel Worksheet Functions | |||
Search multiple values to return single values | Excel Worksheet Functions |