Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up and provide multiple result
Hello again,
I tried to post this, however I don't find it under my postings, hence sending it again. --- I have a worksheet in the following format Name Week Total Jon 4-Sep 1 Philip 4-Sep 2 Dan 4-Sep 3 James 4-Sep 4 Jon 25-Sep 5 James 11-Sep 6 Philip 11-Sep 6 Dan 18-Sep 7 James 18-Sep 8 Philip 25-Sep 8 I want to creat a look up in a different sheet where one can Look up for individuals Total by week as under Case 1 Look up = Philip The result should appear as Week 4-Sep 11-Sep 25-Sep Total 2 6 8 & Case 2 Look up = Jon The result should appear as Week 4-Sep 25-Sep Total 1 5 I tried to use Vlookup, Index/match, but didn't able to achieve what i wanted. If you can help witha formula to get the desired result. Also, since the data I have has multiple weeks info - if I can get last 4 week info. would be great. Regards, Dinesh |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up and provide multiple result
nsd wrote:
Hello again, I tried to post this, however I don't find it under my postings, hence sending it again. --- I have a worksheet in the following format Name Week Total Jon 4-Sep 1 Philip 4-Sep 2 Dan 4-Sep 3 James 4-Sep 4 Jon 25-Sep 5 James 11-Sep 6 Philip 11-Sep 6 Dan 18-Sep 7 James 18-Sep 8 Philip 25-Sep 8 I want to creat a look up in a different sheet where one can Look up for individuals Total by week as under Case 1 Look up = Philip The result should appear as Week 4-Sep 11-Sep 25-Sep Total 2 6 8 & Case 2 Look up = Jon The result should appear as Week 4-Sep 25-Sep Total 1 5 I tried to use Vlookup, Index/match, but didn't able to achieve what i wanted. If you can help witha formula to get the desired result. Also, since the data I have has multiple weeks info - if I can get last 4 week info. would be great. Regards, Dinesh Hi Dinesh, VLookup and Index are not an efficient tools for returning multiple results. However, the format & function you describe is (almost) exactly what a pivot table can do for you; I suggest you give that a try. To automatically filter on [last 4 weeks] you can add a calculated column to your pivot table data that returns true or false based on the date column. Does this make sense? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up and provide multiple result
Check this out:
http://office.microsoft.com/en-us/ex...260381033.aspx -- Gary''s Student - gsnu200908 "smartin" wrote: nsd wrote: Hello again, I tried to post this, however I don't find it under my postings, hence sending it again. --- I have a worksheet in the following format Name Week Total Jon 4-Sep 1 Philip 4-Sep 2 Dan 4-Sep 3 James 4-Sep 4 Jon 25-Sep 5 James 11-Sep 6 Philip 11-Sep 6 Dan 18-Sep 7 James 18-Sep 8 Philip 25-Sep 8 I want to creat a look up in a different sheet where one can Look up for individuals Total by week as under Case 1 Look up = Philip The result should appear as Week 4-Sep 11-Sep 25-Sep Total 2 6 8 & Case 2 Look up = Jon The result should appear as Week 4-Sep 25-Sep Total 1 5 I tried to use Vlookup, Index/match, but didn't able to achieve what i wanted. If you can help witha formula to get the desired result. Also, since the data I have has multiple weeks info - if I can get last 4 week info. would be great. Regards, Dinesh Hi Dinesh, VLookup and Index are not an efficient tools for returning multiple results. However, the format & function you describe is (almost) exactly what a pivot table can do for you; I suggest you give that a try. To automatically filter on [last 4 weeks] you can add a calculated column to your pivot table data that returns true or false based on the date column. Does this make sense? . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up and provide multiple result
Gary''s Student wrote:
Check this out: http://office.microsoft.com/en-us/ex...260381033.aspx Leave it to Ashish! Another great source for this kind of solution is http://www.get-digital-help.com/ My preference would still be the pivot table (^: |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look Up and provide multiple result
Excel 2007 Pivot Table
As suggested by smartin: http://www.mediafire.com/file/zqmbyd...10_24_09a.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple arguments from 2 column to provide an added result from a | Excel Worksheet Functions | |||
multiple arguments from 2 column to provide an added result fr | Excel Worksheet Functions | |||
Provide a match from 2 cells to give a result from another | Excel Worksheet Functions | |||
Having a formula provide a running total from multiple worksheets | Excel Worksheet Functions | |||
How do I Provide a specific result given multiple criteria? | Excel Worksheet Functions |