Index(collect help!

npitula_1

I am trying to get a percentage to pull in based upon 2 criteria, however, the range of percentages is across many columns.

The criteria is a bad debt allocation name, as well as the "aging bucket" (or age of account). I have formulas set and working appropriately to give the allocation name, and aging bucket number, in my "final" sheet. The reference sheet I am using, has a column for the allocation name, then 9 columns for the aging bucket, and the applicable percentages, as these are all different based upon the allocation type, and the age of the account/aging bucket #. I believe I have to reference each column individually, but I am severely struggling with how to get this formula written, or, maybe index(collect isn't what I should be using after all. I have an embedded if(and(today formula calculating my aging bucket # in my final sheet as well, not sure if this why I am having issues since this is a criteria row.

Any and all help would be greatly appreciated!

SSFeatures

Hi npitula_1,

Can you add a screenshot of the sheet, or at least what the "allocation name" column and the 9 percentage columns look like?

My initial thought is that you'll probably want something like this:

=IFERROR(
  INDEX({Percentile1 Range}, MATCH("Allocation 1", {Allocation Range}, 0)),
  INDEX({Percentile2 Range}, MATCH("Allocation 2", {Allocation Range}, 0))
)

This says, look at the value in the Allocation column. If the value is "Allocation 1" return the value from column Percentile1. However, if "Allocation 1" was not found, then check if the value was "Allocation 2", if it is "Allocation 2" then return the value from the Percentile2 column.

Since you have 9 columns you'll need to combine these with more IFERRORs. You'll want something like this:

=IFERROR(
  INDEX({Percentile1 Range}, MATCH("Allocation 1", {Allocation Range}, 0)),
  IFERROR(
    INDEX({Percentile2 Range}, MATCH("Allocation 2", {Allocation Range}, 0)),
    IFERROR(
    . . .
    )
  )
)

Hope this helps!

npitula_1

Here is the sheet I am referencing, there is the Bad Debt Allocation name column, and then each "bucket" has a number for the header, as this is how the number is displayed in my if(and(today formula on the main sheet.

Paul Newcome

How exactly are you determining the aging bucket? The overall formula would be similar to the below, but some of the exact details may need to change based on the aging bucket.

=INDEX(IF([Aging Bucket]@row = 1, {1st % Column}, IF([Aging Bucket]@row = 30, {2nd % Column}, IF([Aging Bucket]@row = 60, {3rd % Column}, IF(……………continue_nested_IF_for_remaining_columns/ranges…………..)))))))), MATCH([Allocation Name]@row, {Allocation Name Column}, 0))