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

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?

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

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 (^:

Look Up and provide multiple result
 

Excel 2007 Pivot Table
As suggested by smartin:
http://www.mediafire.com/file/zqmbyd...10_24_09a.xlsx