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?
.
|