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.

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

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Description[/TD]
[TD]Budget[/TD]
[/TR]
[TR]
[TD]Front Porch[/TD]
[TD]Properly install wire mold at outlet close to ceiling and exterior light[/TD]
[TD]236[/TD]
[/TR]
[TR]
[TD]Living Room[/TD]
[TD]Furnish and install new light switch and cover plate[/TD]
[TD]71
[/TD]
[/TR]
[TR]
[TD]*******[/TD]
[TD]Furnish and install new 240V outlet for range and 120V outlet for fridge[/TD]
[TD]714[/TD]
[/TR]
</tbody>[/TABLE]


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.