Match one value in column and vlookup the rest?

S

Shawn09

Board Regular
Joined
May 13, 2005
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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
AB
2District_2
3Retail$18,657.00
4Discounts$9,257.00
5Food Costs$35,446.00
6Paper Costs$42,762.00
7Linen Costs0
8Uniform Exp0
9Holiday Pay$28,591.00
10Local Tax$30,484.00
Sheet4
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0
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!
 
Upvote 0
Is the number of line items in each district the same? If so, then try:

AB
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.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top