Search among multiple columns

[srkreignz]

Hi
I have a table in Sheet II

Month	Vendor	Diesel	Water	Cleaner
Jan	XXX	1000
Jan	XYZ		1000
Feb	XYZ		1000
Feb	YYY			500
Feb	XYZ		2000
March	XXX	1500

<tbody>
</tbody>

I want a report in Sheet I, when i choose a vendor from the dropdown list (which i have created), I want the sum of the respective item against each month.

Eg: I choose Vendor XYZ

Vendor: XYZ

Month	Load	Item
Jan	1000	Water
Feb	3000	Water
March	0	Water

<tbody>
</tbody>

Please help.
Thanks in advance

 

[mick0005]

Have you defined the item, or are you expecting the formula to pull in the item as well?

How would you expect it to look if the vendor had more than one item, or will this not occur?

 

[srkreignz]

Have you defined the item, or are you expecting the formula to pull in the item as well? How would you expect it to look if the vendor had more than one item, or will this not occur?

Hi,

Since the 3 items are static, I have not defined it. It would be great if a formula can fetch the item.
On vendor part..a vendor cannot have more than one item.

 

[mick0005]

In terms of automatically pulling in the item based on the vendor selection (since each vendor only supplies 1 item) I would just create a reference table. Then you can use index(match to pull in the correct item based on the vendor selection. You'll see I created the vendor referencer in A9:B12. You can put this anywhere, you just need to modify the references to that range in my index(match formula in D16.

The formula which does the summing is an array formula, so when you put it into the function bar, you need to enter it by hitting CTRL+SHIFT+ENTER at the same time. After that you can drag it down.

Excel 2010

	A	B	C	D	E
1	Month	Vendor	Diesel	Water	Cleaner
2	Jan	XXX	1000
3	Jan	XYZ		1000
4	Feb	XYZ		1000
5	Feb	YYY			500
6	Feb	XYZ		2000
7	March	XXX	1500
8
9	Define Vendor Item Here:
10	XXX	Diesel
11	XYZ	Water
12	YYY	Cleaner
13
14
15	Report
16	Choose Vendor	XXX	Vendor Item	Diesel
17
18	Month	Load	Item
19	Jan	1000	Diesel
20	Feb	0	Diesel
21	March	1500	Diesel

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D16 =INDEX($B$10:$B$12,MATCH($B$16,$A$10:$A$12,0)) C19 =IF(A19<>"",$D$16,"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B19 {=SUMPRODUCT(($A$2:$A$7&$B$2:$B$7=A19&$B$16)*($A$1:$R$1=$D$16),$A$2:$R$7)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[srkreignz]

Thanks a lot for the reply Mick...

Is there any way I could do it without a reference table ??

 

[mick0005]

I am sure it can be done, but why not just put the reference table onto another sheet and then hide the sheet? This makes it a lot easier to manage in the long run anyways.

 

[srkreignz]

I know its annoying for you ... but can u plz help me with the other solution where i can define the items??

 

[mick0005]

What do you mean by define the item? Manually type it in instead of having it come in from the reference table automatically? Or do you mean you want it to still come in by using a formula, but you don't want to have a reference table?

I can't really think of a way to do it without a reference table to be honest. Maybe someone else can.

 

[srkreignz]

@Mick I was just trying my luck..

Got it done with ur solution...thanks a tonne for ur replies and ur patience