Match one value in column and vlookup the rest?

Shawn09

Board Regular
Joined
May 13, 2005
Messages
68
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
 

Some videos you may like

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
Joined
Oct 16, 2012
Messages
4,514
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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #574123;;">District_2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Retail </td><td style="text-align: right;;">$18,657.00</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Discounts </td><td style="text-align: right;;">$9,257.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Food Costs </td><td style="text-align: right;;">$35,446.00</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Paper Costs </td><td style="text-align: right;;">$42,762.00</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Linen Costs </td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Uniform Exp </td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Holiday Pay </td><td style="text-align: right;;">$28,591.00</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Local Tax </td><td style="text-align: right;;">$30,484.00</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A3,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A4,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A5,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A6,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A7,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A8,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A9,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">$A10,INDIRECT(<font color="Green">$A$2</font>),2,0</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Just to add, the formula in B3 is entered there and pulled down.

Howard
 

Shawn09

Board Regular
Joined
May 13, 2005
Messages
68
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
Joined
Aug 18, 2015
Messages
9,570
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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</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),"")

<thead>
</thead><tbody>
</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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,822
Messages
5,446,710
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top