# Match one value in column and vlookup the rest?

#### Shawn09

##### Board Regular
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

Shawn

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### L. Howard

##### Well-known Member
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

Last edited:

#### L. Howard

##### Well-known Member
Just to add, the formula in B3 is entered there and pulled down.

Howard

#### Shawn09

##### Board Regular
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

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

AB
1
2District 3
3Retail\$17,353.00
4Discounts\$20,674.00
5Food Costs
6Paper Costs
7Linen Costs\$40,907.00
8Uniform Exp\$43,899.00
9Holiday Pay\$22,528.00
10Local Tax\$7,847.00
11
12****************************************
13District 1
14Retail\$14,514.00
15Discounts\$6,986.00
16Local Tax\$28,626.00
17Holiday Pay\$42,159.00
18Linen Costs\$42,375.00
19Uniform Exp\$29,253.00
20
21District 2
22Retail\$18,657.00
23Discounts\$9,257.00
24Local Tax\$30,484.00
25Holiday Pay\$28,591.00
26Food Costs\$35,446.00
27Paper Costs\$42,762.00
28
29District 3
30Retail\$17,353.00
31Discounts\$20,674.00
32Local Tax\$7,847.00
33Holiday Pay\$22,528.00
34Linen Costs\$40,907.00
35Uniform Exp\$43,899.00
36
37District 4
38Retail\$8,590.00
39Food Costs
40Paper Costs\$23,199.00
41Holiday Pay\$25,844.00
42Linen Costs\$17,045.00
43Uniform Exp\$18,899.00

</tbody>
Sheet4

Worksheet Formulas
CellFormula
B3=IFERROR(VLOOKUP(A3,OFFSET(\$A\$13,MATCH(\$A\$2,\$A\$13:\$A\$10000,0),0,8,2),2,0),"")

</tbody>

<tbody>
</tbody>

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

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