I have searched for a solution but am coming up blank. I have two worksheets that I need to do a search From one, using 3 criteria, to the Other sheet and bring in the value that matches.
Sheet with the Formula that I tried:
The sheet with the Values that I need to bring in to Row 9:
As you can see my attempt doesn't work. the info and Formula on Crew 4 sheet will extend to Col Q.
The data on LABOR sheet is variable (could be longer than 21 Rows) and is not consistent if you notice that info between Rows 3 and 12 is 10 items and between Rows 13 and 21 is 9 items. Other entries could be more or less items.
As I type this question, I'm not sure if a Formula (preferred) is the proper approach. I'm open for a macro but I would think it would have to be in the Worksheet Code Window since these two sheets would be copied to a New Workbook and sent to individuals to fill in the info on the Crew sheet.
Any help or guidance is much appreciated.
Sheet with the Formula that I tried:
The sheet with the Values that I need to bring in to Row 9:
Cell Formulas | ||
---|---|---|
Range | Formula | |
C9 | {=INDEX(LABOR!$A$3:$E$21,MATCH(C5,C7&C8,),0)} | |
D9 | {=INDEX(LABOR!$A$3:$E$21,MATCH(D5,D7&D8,),0)} | |
E9 | {=INDEX(LABOR!$A$3:$E$21,MATCH(E5,E7&E8,),0)} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Excel 2010 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
2 | Lot / Block | Address | Code | Description | Budget | ||
3 | 87/45 | 222 ANY STREET | LH10 | Lath Labor | 1,100.00 | ||
4 | LH30 | Trim Labor | 196.00 | ||||
5 | LH80 | Staple Labor | 67.00 | ||||
6 | ST10 | Masking Labor | 45.00 | ||||
7 | ST30 | Brown Labor | 1,010.00 | ||||
8 | ST50 | Finish Labor | 938.00 | ||||
9 | ST70 | Water | 34.00 | ||||
10 | SC10 | Scaffold Erect | 256.00 | ||||
11 | SC20 | Scaffold Down | 86.00 | ||||
12 | ST90 | Cleanup | 22.00 | ||||
13 | 87/C | 222 ANY STREET | LH10 | Lath Labor | 519.00 | ||
14 | LH30 | Trim Labor | 103.00 | ||||
15 | LH80 | Staple Labor | 24.00 | ||||
16 | ST10 | Masking Labor | 8.00 | ||||
17 | ST30 | Brown Labor | 375.00 | ||||
18 | ST50 | Finish Labor | 348.00 | ||||
19 | ST70 | Water | 12.00 | ||||
20 | SC10 | Scaffold Erect | 108.00 | ||||
21 | SC20 | Scaffold Down | 36.00 | ||||
LABOR |
As you can see my attempt doesn't work. the info and Formula on Crew 4 sheet will extend to Col Q.
The data on LABOR sheet is variable (could be longer than 21 Rows) and is not consistent if you notice that info between Rows 3 and 12 is 10 items and between Rows 13 and 21 is 9 items. Other entries could be more or less items.
As I type this question, I'm not sure if a Formula (preferred) is the proper approach. I'm open for a macro but I would think it would have to be in the Worksheet Code Window since these two sheets would be copied to a New Workbook and sent to individuals to fill in the info on the Crew sheet.
Any help or guidance is much appreciated.