Find value in one column and return value from same row but different column in another tab

[Vickie Hartley]

I work for a non-profit community housing agency and created a workbook that has multiple tabs. One of the tabs is a spreadsheet that contains 22 columns (scope assumptions). On another tab I want to pull data from this large spreadsheet. There are usually at least a 100 rows, each room in a house has multiple lines and tasks with sub-totals by room. The spreadsheets primary purpose is to communicate to the construction crew what needs to be done in each room and what the budget is. Here is an example of the primary spreadsheet.

Location	Task	Description	Labor Hours	Labor Costs	Materials	Subs	OH,G&A, Profit	Contract Amount
Front Porch	Demolition	Demo garbage, existing windows, steps	3	90	50		60	200
Front Porch	Electrical	Properly install wire mold at outlet close to ceiling and exterior light				165	71	236
	Sub-Total			90	50	165	131	436
Living Room	Electrical	Furnish and install new light switch and cover plate				50	21	71
Living Room	Doors & Windows	Furnish and install new trim and windows (3)	8	300	300		257	857
	Sub-Total		8	300	300	50	278	928
*******	Electrical	Furnish and install new 240V outlet for range and 120V outlet for fridge				500	214	714
*******	Plumbing	******* sink - Furnish and install new PVC drain, vent and pex supply lines with shut off valves and all sundries for complete install				1050	450	1500
	Sub-Total		0	0	0	1550	664	2214

<tbody>
</tbody>

A second, third and fourth spreadsheet is needed to communicate to the sub-contractors what needs to be done. One spreadsheet each for Electrical, Plumbing, and HVAC. Ideally I want to create formulas that will look for every occurrence of a given task (i.e. "Electrical") in column B from the primary spreadsheet and return the corresponding location, description, and cost for each occurrence. For example:

Electrical Scope

Location	Description	Budget
Front Porch	Properly install wire mold at outlet close to ceiling and exterior light	236
Living Room	Furnish and install new light switch and cover plate	71
*******	Furnish and install new 240V outlet for range and 120V outlet for fridge	714

<tbody>
</tbody>

Can you please give me some guidance and/or answers?

I often use Mr. Excel to solve my Excel problems by looking for similar problems or using the books I have purchased but this time, I could not find a solution so I posted my first request for help. I have been using this site for about 3 years.

I have tried a multitude of nested formulas (search, if, lookup, index, etc) to get this to work.

 

[strooman]

I have not had any luck determining what is driving the error.

Here is my formula.
=IF('Original scope assumptions'!$AE$37="Electrical",IF(ROWS(A$2:A2)<='Original scope assumptions'!$AF$37,INDEX(INDIRECT(A$1),SMALL(IF(Task='Original scope assumptions'!$AE$37,ROW(Task)-ROW('Original scope assumptions'!$B$37)+1),ROWS(A$2:A2))),""),"")

First, did you enter the Formula with the keyboard combination Ctrl+Shift+Enter?
When you just hit Enter you get the #VALUE! error.

Second, make sure your headers match. The headers from the table on the "Original scope assumptions" sheet should be the same on the "Electrical" sheet otherwise you get the #REF! error.

Wrong: Just Enter


Correct: Ctrl+Shift+Enter

 

[Vickie Hartley]

I'm not sure how to answer your question. I can't say if its standard or sheet. I would be happy to email you the file.

 

[strooman]

I had a look at the workbook. Some problems I noticed so others can perhaps benefit from it:

First, You must enter the formula with the keyboard combination Ctrl+Shift+Enter. Not just Enter. This is essential otherwise the formula won't work.

Second, The lookup table starts in row 35 with the headers. So I adjusted a cell reference from $B$37 to $B$36 where the actual data starts.

=IF('Original scope assumptions'!$AE$37="Electrical",IF(ROWS(A$2:A2)<='Original scope assumptions'!$AF$37,INDEX(INDIRECT(A$1),SMALL(IF(Task='Original scope assumptions'!$AE$37,ROW(Task)-ROW('Original scope assumptions'!$B$36)+1),ROWS(A$2:A2))),""),"")

Third, The Location Column (Column A) contained just 1 row with the location. That is why it couldn't find the location because it was referring to a blank row.

Fourth and last, I couldn't find the Budget column so I put there the _1_Roof_Contract_Amount column.

Excel is very picky with names. You have to spell them exactly the same.

 

[Vickie Hartley]

Thanks! I have learned two great techniques - that work. You have been wonderful.