INDEX Collect

Archie Villa

Hi GURUS

here is my data ( FIG 1)from another sheet, i am trying to build in another sheet ( FIG2) using the INDEX and COLLECT fmla, however i kept on getting #INCORRECT ARGUMENT SET.

FIG1:

FIG2

my fmla were :

=INDEX(Collect("my range is the column of row ID up to the column of #7 in FIG1", "Row id", "code"),1)

=index(collect("my range is the column of row ID up to the column of #7 in FIG1","range of row ID in FIG1,"row iD in FIG2","column of ROw ID fig1",column of code in FIG2),1)

thank you for your assistance

@Paul Newcome

@Andrée Starå

Archie Villa

Hi Paul

i followed the formula, however when i drag it down, it does not match

=INDEX([PHYSICAL ACCESS CONTROLS]12:[Column15]23, MATCH(CODE$1, [THREAT AWARENESS]$12:[THREAT AWARENESS]23, 0), MATCH([Row ID]2, [PHYSICAL ACCESS CONTROLS]$11:[Column15]$11, 0))

here is my link

https://app.smartsheet.com/b/publish?EQBCT=98c57d20483c45b6a6ecc13cf889e335

thank you

Paul Newcome

Can you post the actual formulas?

Archie Villa

Hi Paul

here it is

1. =INDEX(COLLECT({PMI_C-TPAT_Self_Assessment_Scores Range 7}, [Row ID]2, CODE1), 1)
2. =INDEX(COLLECT({PMI_C-TPAT_Self_Assessment_Scores Range 7}, {PMI_C-TPAT_Self_Assessment_Scores Range 10}, [Row ID]2, {PMI_C-TPAT_Self_Assessment_Scores Range 4}, CODE1), 1)

i patterned my formula from an Index match match.

thank you

Paul Newcome

And what exactly does range 7 and range 4 cover?

Archie Villa

Hi Paul

range 7 is this

range 10 is this

range 4 is this

Paul Newcome

That's the problem. The ranges within a function must all be of the same size and shape. Based on my understanding of the above, you are going to probably end up using an INDEX/MATCH/MATCH.

Because they are separate functions (even though they are in the same formula), you can use different size/shaped ranges.

It would end up looking something like this...

=INDEX({inside of table}, MATCH([CODE]$1, {Range 10}, 0), MATCH([Row ID]@row, {Range 4}, 0))

Archie Villa

Hi Paul

i followed the formula, however when i drag it down, it does not match

=INDEX([PHYSICAL ACCESS CONTROLS]12:[Column15]23, MATCH(CODE$1, [THREAT AWARENESS]$12:[THREAT AWARENESS]23, 0), MATCH([Row ID]2, [PHYSICAL ACCESS CONTROLS]$11:[Column15]$11, 0))

here is my link

https://app.smartsheet.com/b/publish?EQBCT=98c57d20483c45b6a6ecc13cf889e335

thank you

Paul Newcome

You are going to want to make sure you are "locking in" the appropriate ranges using a $ before column names and/or row numbers as needed. If you are dragging DOWN, then you will want to put the $ before the row numbers in your first range.

Archie Villa

Hi Paul

i forgot that one, haha

Anyway, thank you for your assistance.

regards

archie