Match one value in column and vlookup the rest?

[Shawn09]

Not sure if my title is exactly what I'm looking for but what I'm trying to do is lookup a value based on two criteria. The best way to show you what I'm looking to do is to just show a sample of what I'm trying to do. Whatever is in cell A2 (District 3), I want the formulas in column B to lookup in all of column A, the corresponding values that match whatever district and their values in all of column B. If there is nothing to match it just needs to be 0... I'm able to do that part with an ISNA and VLOOKUP formula but I am unsure how to also match it to the district. Any help would be appreciated.

Column A Column B

District 3
Retail $17,353.00
Discounts $20,674.00
Food Costs $-
Paper Costs $-
Linen Costs $40,907.00
Uniform Exp $43,899.00
Holiday Pay $22,528.00
Local Tax $7,847.00

****************************************
District 1
Retail $14,514.00
Discounts $6,986.00
Local Tax $28,626.00
Holiday Pay $42,159.00
Linen Costs $42,375.00
Uniform Exp $29,253.00

District 2
Retail $18,657.00
Discounts $9,257.00
Local Tax $30,484.00
Holiday Pay $28,591.00
Food Costs $35,446.00
Paper Costs $42,762.00

District 3
Retail $17,353.00
Discounts $20,674.00
Local Tax $7,847.00
Holiday Pay $22,528.00
Linen Costs $40,907.00
Uniform Exp $43,899.00

District 4
Retail $8,590.00
Food Costs $29,239.00
Paper Costs $23,199.00
Holiday Pay $25,844.00
Linen Costs $17,045.00
Uniform Exp $18,899.00


Thanks in advance!

Shawn

 

[L. Howard]

This works for me on my test sheet.

Name each district, I used District_1, District_2 etc. where the named range is both column A and B data for each district.
Cell A2 is a drop down with all the District_X names.

Howard

Excel 2012
	A	B
2	District_2
3	Retail	$18,657.00
4	Discounts	$9,257.00
5	Food Costs	$35,446.00
6	Paper Costs	$42,762.00
7	Linen Costs	0
8	Uniform Exp	0
9	Holiday Pay	$28,591.00
10	Local Tax	$30,484.00
Sheet4

Cell Formulas
Range	Formula
B3	=IFERROR(VLOOKUP($A3,INDIRECT($A$2),2,0),0)
B4	=IFERROR(VLOOKUP($A4,INDIRECT($A$2),2,0),0)
B5	=IFERROR(VLOOKUP($A5,INDIRECT($A$2),2,0),0)
B6	=IFERROR(VLOOKUP($A6,INDIRECT($A$2),2,0),0)
B7	=IFERROR(VLOOKUP($A7,INDIRECT($A$2),2,0),0)
B8	=IFERROR(VLOOKUP($A8,INDIRECT($A$2),2,0),0)
B9	=IFERROR(VLOOKUP($A9,INDIRECT($A$2),2,0),0)
B10	=IFERROR(VLOOKUP($A10,INDIRECT($A$2),2,0),0)

 

[L. Howard]

Just to add, the formula in B3 is entered there and pulled down.

Howard

 

[Shawn09]

Thank you for that. I see how that works as I was able to replicate what you did. Unfortunately it really only works on this sample size... the original sheet has a lot more items and has about 10,000 rows of data so I wouldn't be able to do named ranges for each of them. Know of another way to do it possibly? Thank you again for your suggestion!

 

[Eric W]

Is the number of line items in each district the same? If so, then try:

	A	B
District 3
Retail
Discounts
Food Costs
Paper Costs
Linen Costs
Uniform Exp
Holiday Pay
Local Tax
****************************************
District 1
Retail
Discounts
Local Tax
Holiday Pay
Linen Costs
Uniform Exp
District 2
Retail
Discounts
Local Tax
Holiday Pay
Food Costs
Paper Costs
District 3
Retail
Discounts
Local Tax
Holiday Pay
Linen Costs
Uniform Exp
District 4
Retail
Food Costs
Paper Costs
Holiday Pay
Linen Costs
Uniform Exp

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$17,353.00[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$20,674.00[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$40,907.00[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$43,899.00[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$22,528.00[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]$7,847.00[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]$14,514.00[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]$6,986.00[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]$28,626.00[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]$42,159.00[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]$42,375.00[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]$29,253.00[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]$18,657.00[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]$9,257.00[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]$30,484.00[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]$28,591.00[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]$35,446.00[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]$42,762.00[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]$17,353.00[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]$20,674.00[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]$7,847.00[/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]$22,528.00[/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]$40,907.00[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]$43,899.00[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]$8,590.00[/TD]

[TD="align: center"]39[/TD]

[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]$23,199.00[/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]$25,844.00[/TD]

[TD="align: center"]42[/TD]

[TD="align: right"]$17,045.00[/TD]

[TD="align: center"]43[/TD]

[TD="align: right"]$18,899.00[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(A3,OFFSET($A$13,MATCH($A$2,$A$13:$A$10000,0),0,8,2),2,0),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The 8 refers to the number of line items in each section. If they can vary, it becomes much trickier to figure out where each section ends, but it can be done.

 

[Shawn09]

Thank you! This works for what I am needed it to do!