Hi all,
I'm running into an issue with some lookup I'm doing using INDEX MATCH which are stopping after they find the first result. I need this function to continue searching through the data for ALL possible matches to give me the result I need. I understand that an array formula is probably what I need, but I'm not certain and the work I've done so far to put one together has not worked properly.
The formula I'm using is as follows, and below is a rough idea of my table layout.
=IF(AND(INDEX('Cost Pool Account Set Line'!$B:$B,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))>=Summary!$A452,INDEX('Cost Pool Account Set Line'!$C:$C,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))<=Summary!$A452),"X","")
Summary = my "working" sheet which is summarizing the information.
Cost Pool Account Set Line = Table pulled in from power query on a separate sheet within the workbook
Summary Sheet Layout - I need the formula to identify that there are entries in the Cost Pool Account Set Line for LABOR not just for the 10000 (which it's doing now), but also 11000 and 11010.
<tbody>
</tbody>
Cost Pool Account Set Line
<tbody>
</tbody>
The formula above may be overly cumbersome for what I'm trying to achieve, if there are simplification suggestions I'm happy to hear them!
Hopefully this make some sense, if not please let me know if there is additional information I can provide to help.
Thank you!
I'm running into an issue with some lookup I'm doing using INDEX MATCH which are stopping after they find the first result. I need this function to continue searching through the data for ALL possible matches to give me the result I need. I understand that an array formula is probably what I need, but I'm not certain and the work I've done so far to put one together has not worked properly.
The formula I'm using is as follows, and below is a rough idea of my table layout.
=IF(AND(INDEX('Cost Pool Account Set Line'!$B:$B,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))>=Summary!$A452,INDEX('Cost Pool Account Set Line'!$C:$C,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))<=Summary!$A452),"X","")
Summary = my "working" sheet which is summarizing the information.
Cost Pool Account Set Line = Table pulled in from power query on a separate sheet within the workbook
Summary Sheet Layout - I need the formula to identify that there are entries in the Cost Pool Account Set Line for LABOR not just for the 10000 (which it's doing now), but also 11000 and 11010.
Number | LABOR | FRINGE |
10000 | X | |
10100 | X | |
11000 | X | |
11010 | X |
<tbody>
</tbody>
Cost Pool Account Set Line
From Account | To Account | Cost Pool Account Set Name |
10000 | 10099 | LABOR |
10100 | 10100 | FRINGE |
10999 | 11999 | LABOR |
<tbody>
</tbody>
The formula above may be overly cumbersome for what I'm trying to achieve, if there are simplification suggestions I'm happy to hear them!
Hopefully this make some sense, if not please let me know if there is additional information I can provide to help.
Thank you!