Looking up a variable that has multiple values?

[Mangolili]

[TABLE="width: 189"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Skincare
[/TD]
[TD]1500
[/TD]
[/TR]
[TR]
[TD]7 Magazine
[/TD]
[TD]120
[/TD]
[/TR]
[TR]
[TD]Cosmo
[/TD]
[TD]345
[/TD]
[/TR]
[TR]
[TD]SkinCare Magz
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HairCare
[/TD]
[TD]2300
[/TD]
[/TR]
[TR]
[TD]Hairstylist Mag
[/TD]
[TD]124
[/TD]
[/TR]
[TR]
[TD]HairCare Mag
[/TD]
[TD]234
[/TD]
[/TR]
[TR]
[TD]Shampoo Guide
[/TD]
[TD]453
[/TD]
[/TR]
[TR]
[TD]7 Magazine
[/TD]
[TD]340
[/TD]
[/TR]
</tbody>[/TABLE]

My Questions is if I want to look up the value for 7 magazine, how do I do it? I tried vlook up, but it will work if I am looking up 7 Magazine and it has one value, or if I select a specfict category for that 7 magazine. In my data, 7 magazine is under each categorie like Skincare and HairCare. Each 7 Magazine has different values. It would be great if there's something for me to look up the entire sheet instead of spefici category. Above it an example of my data. Please help if you have any ideas or suggestions. Thank you so much.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sal Paradise]

Are you looking for the sum? The average? An array of the values? What are you trying to do with the info?

Excel 2010
	A	B	C	D	E	F	G
1	Skincare	1500		Name	Sum	Average	Variable
2	7 Magazine	120		7 Magazine	460	230	FALSE
3	Cosmo	345				Contents:	{FALSE;120;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;340}
4	SkinCare Magz	123
5	HairCare	2300
6	Hairstylist Mag	124
7	HairCare Mag	234
8	Shampoo Guide	453
9	7 Magazine	340
Sheet1

Cell Formulas
Range	Formula
E2	=SUMIF(A:A,$D2,B:B)
F2	=SUMIF(A:A,$D2,B:B)/COUNTIF(A:A,$D2)
G2	{=IF(A1:A9=D2,B1:B9)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

What is the desired result for 7 Magazine, given your sample?

 

[Mangolili]

The result is looking up the value of each 7 magazine. Displaying the value for 7 magazine for each category.

Like: SkinCare 1500 7 magazine 120
HairCare 2300 7 magazine 340

For Skincare and Haircare values, I can use vlookup. Not sure how to get the 7 magazine values.

Thanks.

 

[Aladin Akyurek]

The result is looking up the value of each 7 magazine. Displaying the value for 7 magazine for each category.

Like: SkinCare 1500 7 magazine 120
HairCare 2300 7 magazine 340

For Skincare and Haircare values, I can use vlookup. Not sure how to get the 7 magazine values.

Thanks.

A data structure like below would make your life easier...

[TABLE="width: 541"]
<colgroup><col style="width: 148pt; mso-width-source: userset; mso-width-alt: 6997;" width="197"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <col style="width: 73pt; mso-width-source: userset; mso-width-alt: 3441;" width="97"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;" width="90"> <col style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;" width="129"> <tbody>[TR]
[TD="class: xl65, width: 197, bgcolor: white"]ITEM[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]VALUE[/TD]
[TD="class: xl67, width: 97, bgcolor: transparent"]CATEGORY[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 90, bgcolor: transparent"]CATEGORY[/TD]
[TD="class: xl67, width: 129, bgcolor: transparent"]DESCRIPTION[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]7 Magazine[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]120[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]Skin Care[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]Cosmo[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]345[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"]Hair care[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]SkinCare Magz[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]123[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]Hairstylist Mag[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]124[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]HairCare Mag[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]234[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]Shampoo Guide[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]453[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 197, bgcolor: white"]7 Magazine[/TD]
[TD="class: xl66, width: 79, bgcolor: white"]340[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Mangolili]

Thank you so much for your advice. But the report is generated from a site and I am pulling information from it. It's a big report, maybe I can use the data structure you provided and play with the data and see if I can do something about it.

 

[Aladin Akyurek]

Thank you so much for your advice. But the report is generated from a site and I am pulling information from it. It's a big report, maybe I can use the data structure you provided and play with the data and see if I can do something about it.

You are welcome.