Index Match with 3 Criteria

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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:

<b>Excel 2010</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 /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">TEST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">TEST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">TEST</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">87/45</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">87/C</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">87/45</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8001A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8001A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8001A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">LH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">ST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">SC</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">50</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">#VALUE!</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">#VALUE!</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">#VALUE!</td></tr></tbody></table><p style="width:4.2em;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">Crew 4 </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>Array 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">C9</th><td style="text-align:left">{=INDEX(<font color="Blue">LABOR!$A$3:$E$21,MATCH(<font color="Red">C5,C7&C8,</font>),0</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D9</th><td style="text-align:left">{=INDEX(<font color="Blue">LABOR!$A$3:$E$21,MATCH(<font color="Red">D5,D7&D8,</font>),0</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E9</th><td style="text-align:left">{=INDEX(<font color="Blue">LABOR!$A$3:$E$21,MATCH(<font color="Red">E5,E7&E8,</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b>Excel 2010</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 /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lot / Block</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Address</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Code</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Description</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Budget</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">87/45</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">222 ANY STREET</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> LH10 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Lath Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 1,100.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> LH30 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Trim Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 196.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> LH80 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Staple Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 67.00 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST10 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Masking Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 45.00 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST30 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Brown Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 1,010.00 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST50 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Finish Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 938.00 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST70 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Water </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 34.00 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> SC10 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Scaffold Erect </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 256.00 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> SC20 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Scaffold Down </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 86.00 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST90 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Cleanup </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 22.00 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">87/C</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">222 ANY STREET</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> LH10 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Lath Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 519.00 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> LH30 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Trim Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 103.00 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> LH80 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Staple Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 24.00 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST10 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Masking Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 8.00 </td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST30 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Brown Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 375.00 </td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST50 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Finish Labor </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 348.00 </td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> ST70 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Water </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 12.00 </td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> SC10 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Scaffold Erect </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 108.00 </td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> SC20 </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> Scaffold Down </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> 36.00 </td></tr></tbody></table><p style="width:3em;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">LABOR</p><br /><br />

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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
{=INDEX(LABOR!$A$3:$E$21,MATCH(C5,C7&C8,),0)}

The MATCH function's format is as such:

MATCH(lookup_value,lookup_array,type)

You are using C5 as your only lookup value and under lookup array you are using C7&C8... the lookup array should be column A for the C5 and column C for the C7&C8...


Also, the first part of your INDEX function should be the array you want to return data from... I'm not sure what you're looking for... do you want to return the address? Or the description? That is the array you should enter in the first part of your INDEX formula... so for instance, if you wanted to find the description that matches both C5 in column A, and also matches C7&C8 under column C... the formula might look something like this:

=INDEX(LABOR!$D:$D,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0))

CTRL+SHIFT+ENTER



edit: actually I must add that this formula will only work if column A does not have all those blanks... you would have to arrange your sheet so that "87/45" is filled down all the way to row 12, and that "87/C" is filled down to row 21... etc etc... because the formula is looking for a value in that column...
 
Last edited:

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
Thanks for your reply,

I guess I didn't specify what I wanted to Return. I want to return the value that is in Col. E (Budget)

Unfortunately column A is populated by a macro that does not populate all the way down to the next change in that column.

I was hoping that I could accomplish this task without changing the macro that populates the Labor sheet.

Thank you for the education on the Index Match formula. I tried it with the appropriate Col for the return value (Col E), but of course it errors because of Col A not being populated.

<b>Excel 2010</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 /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">TEST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">TEST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">TEST</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">87/45</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">87/C</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">87/45</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8001A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8001A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">8001A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">LH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">ST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">SC</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">50</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">1,100.00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">#N/A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">#N/A</td></tr></tbody></table><p style="width:4.2em;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">Crew 4 </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>Array 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">C9</th><td style="text-align:left">{=INDEX(<font color="Blue">LABOR!$E:$E,MATCH(<font color="Red">C7&C8,IF(<font color="Green">LABOR!$A:$A=C5,LABOR!$C:$C</font>),0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D9</th><td style="text-align:left">{=INDEX(<font color="Blue">LABOR!$E:$E,MATCH(<font color="Red">D7&D8,IF(<font color="Green">LABOR!$A:$A=D5,LABOR!$C:$C</font>),0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E9</th><td style="text-align:left">{=INDEX(<font color="Blue">LABOR!$E:$E,MATCH(<font color="Red">E7&E8,IF(<font color="Green">LABOR!$A:$A=E5,LABOR!$C:$C</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Is there another way to accomplish this so I don't have to change the macro that populates the Labor sheet?

The way that the Labor sheet is set up depends on other macros that does other things. So changing the Labor sheet is not an option unless I change a lot of other coding, which I really don't want to do.
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
Okay, I rewrote my code to populate the Labor sheet to fill Col A. It now looks like this:
<b>Excel 2010</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 /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #3366FF;;">DRRC15013</td><td style="font-weight: bold;text-align: center;color: #3366FF;;">Robon Ranch-Denton</td><td style="font-weight: bold;text-align: right;color: #3366FF;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Lot / Block</td><td style="font-weight: bold;;">Address</td><td style="font-weight: bold;;">Code</td><td style="font-weight: bold;;">Description</td><td style="font-weight: bold;;">Budget</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">33/47</td><td style=";">12505 Ridgedale</td><td style=";"> LH10 </td><td style=";"> Lath Labor </td><td style="text-align: right;;"> 758.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> LH30 </td><td style=";"> Trim Labor </td><td style="text-align: right;;"> 205.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> LH80 </td><td style=";"> Staple Labor </td><td style="text-align: right;;"> 38.00 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> ST10 </td><td style=";"> Masking Labor </td><td style="text-align: right;;"> 35.00 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> ST30 </td><td style=";"> Brown Labor </td><td style="text-align: right;;"> 610.00 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> ST50 </td><td style=";"> Finish Labor </td><td style="text-align: right;;"> 262.00 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> SC10 </td><td style=";"> Scaffold Erect </td><td style="text-align: right;;"> 54.00 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">33/47</td><td style="text-align: right;;"></td><td style=";"> SC20 </td><td style=";"> Scaffold Down </td><td style="text-align: right;;"> 18.00 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">32/47</td><td style=";">12501 Ridgedale</td><td style=";"> LH10 </td><td style=";"> Lath Labor </td><td style="text-align: right;;"> 753.00 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> LH30 </td><td style=";"> Trim Labor </td><td style="text-align: right;;"> 178.00 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> LH80 </td><td style=";"> Staple Labor </td><td style="text-align: right;;"> 37.00 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> ST10 </td><td style=";"> Masking Labor </td><td style="text-align: right;;"> 38.00 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> ST30 </td><td style=";"> Brown Labor </td><td style="text-align: right;;"> 601.00 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> ST50 </td><td style=";"> Finish Labor </td><td style="text-align: right;;"> 258.00 </td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> SC10 </td><td style=";"> Scaffold Erect </td><td style="text-align: right;;"> 54.00 </td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">32/47</td><td style="text-align: right;;"></td><td style=";"> SC20 </td><td style=";"> Scaffold Down </td><td style="text-align: right;;"> 18.00 </td></tr></tbody></table><p style="width:3em;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">LABOR</p><br /><br />

I have also adjusted my formula a bit on the Crew 4 sheet ROW 9:

={IFERROR(INDEX(LABOR!$E:$E,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0)),"")}


This all works just fine.

Is there a way to add in a 4th criteria. I need to check if the entry on the Crew Sheet Row 4 matches what is on the Labor Sheet cell A1.

I'm hoping that I don't have to change the layout of the LABOR sheet because that would require a lot more modification to existing code that makes the LABOR sheet. Which other code depends on the current set up.
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
I need to check if the entry on the Crew Sheet Row 4 matches what is on the Labor Sheet cell A1.

I'm assuming this would mean cells C4, D4, and E4 in your first example of the crew sheet where you originally wrote TEST... in that case, you could just add an IF statement at the beginning, like:


=IFERROR(IF(C4=LABOR!$A$1,INDEX(LABOR!$E:$E,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0)),""),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

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
Top