Hi all,
I'm trying to create a matrix of data to identify whenever a specific account number is included within a certain cost pool. The tricky part is the cost pool accounts have a range associated with them and the accounts are often located within multiple cost pools. This range could be effectively zero (for example it starts at 10000 and ends at 10000) or it could be several hundred. The account number I'm looking for may be equal to the min or max of the range but more often it's located somewhere between the min and max.
I've created a formula using IF AND INDEX and MATCH to help me isolate the account and the pool it's in and then place an "X" within the cell on the matrix (Summary sheet). The problem is the formula always finds the first account that matches which subsequently results in the formula failing the logic of the IF statement after the first match is found.
I've attached a trimmed version of the spreadsheet I'm working with to help explain my problem. You'll see there is a Fringe Base column (C) on the Summary tab. The formula in the cells of column C are looking at the Cost Pool sheet. It's correctly returning the first entry in the Cost Pool sheet (account 15070) and placing an X in that cell, but it then ignores the remaining, correct, entries from the Cost Pool sheet further down the account listing on the Summary sheet where it needs to have an X in each cell for which the account on the Summary sheet is within the range listed on the Cost Pool sheet.
Any help is appreciated!
Bookmarks