Using INDIRECT to refer to INDEX's output

[Libelnon]

As part of generating a list of matching values in an INDEX list, I've had the idea of checking the list from the last matching value's position and continuing down the list from there. I know the INDEX returns a cell value, but is there some way to access that or force it to output it so that another cell can use an INDIRECT reference to see 'where that got to', as such?

Let me explain in more detail. I'm building an interactive character sheet for a roleplaying game, in which all of the abilities that a character would have are listed on a hidden sheet. The INDEX function references this sheet, and searches the entire sheet for items that match the class of the character via the following formula: =INDEX('Hidden Sheet'!A1:F4,MATCH(B2,'Hidden Sheet'!C1:C4,0),1,1). Now, this cell returns the value of the first matching value it finds on the hidden sheet, which is fair enough.

Now, INDEX returns a cell reference, right? So, the cell underneath should be able to do the following: =INDEX((INDIRECT(A150)):'Hidden Sheet'!F4,MATCH(B2,'Hidden Sheet'!C1:C4,0),1,1). In theory, this should get the location that the last cell was referred to (which will be in the A column) as the starting value of the array, and then continue from there. Instead, it accesses the cell and gets the value of the INDEX function, and not the cell reference, meaning it just returns a big, fat #REF! error rather than doing as it's told.

Can anyone suggest how to either rewrite the formula, so that the cell underneath returns the next value that matches the criteria, or to allow me to force the cell to return the value I want, so that the cell underneath can reference that and use it to continue the search?

[FDibbins]

Hi and welcome to the forum

Now, INDEX returns a cell reference, right?

No, INDEX returns the contents of the cell found at the intersection of a given row and column

[FDibbins]

If you want to "find" the results of that INDEX/MATCH, then you would need to make that result the criteria for the MATCH() in another I/M

[Izandol]

INDEX does return a reference to a cell. However it does not return a cell address (or name), which is the required input for INDIRECT. MATCH is most useful as FDibbins said.

[Libelnon]

Okay, as an extension of that, then, how can I use the MATCH function to return part of an array? So far I have this, but it returns an error and won't let me put it in: =INDEX(ADDRESS(MATCH(A150,'Hidden Sheet'!A1:A4,0),1,1,1,"Hidden Sheet"):'Hidden Sheet'!F4,MATCH(B2,'Hidden Sheet'!C1:C4,0),1,1)

[newdoverman]

It seems to me that ADDRESS is what you are looking for as it seems you know the value your are looking for in an array. Something like this might be adaptable for your purposes.

Array formula:

If you know the value and enter it in C1 then this will find the value in an array returning an ABSOLUTE reference.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This will return a RELATIVE reference.

[daddylonglegs]

If you want to return multiple values for multiple matches the normal way is to use an array formula like this

=IFERROR(INDEX('Hidden Sheet'!A$1:A$4,SMALL(IF('Hidden Sheet'!C$1:C$4=B$2,ROW('Hidden Sheet'!C$1:C$4)-ROW('Hidden Sheet'!C$1)+1),ROWS(K$5:K5))),"")

confirm with CTRL+SHIFT+ENTER and copy down column - if you run out of matches you get blanks - I'm assuming you put the first formula in K5, change the red part of the formula to match the start cell

[Tony Valko]

No, INDEX returns the contents of the cell found at the intersection of a given row and column

INDEX does return a reference but depending on how it's used the reference is usually resolved to the cell value.

A good example of how it returns a reference is in defining a dynamic range.

=A1:INDEX(A:A,MATCH("zzzzz",A:A))

[Libelnon]

If you want to return multiple values for multiple matches the normal way is to use an array formula like this

=IFERROR(INDEX('Hidden Sheet'!A$1:A$4,SMALL(IF('Hidden Sheet'!C$1:C$4=B$2,ROW('Hidden Sheet'!C$1:C$4)-ROW('Hidden Sheet'!C$1)+1),ROWS(K$5:K5))),"")

confirm with CTRL+SHIFT+ENTER and copy down column - if you run out of matches you get blanks - I'm assuming you put the first formula in K5, change the red part of the formula to match the start cell

Trying that solution, I've got a long list of potential values going from CL1 to CL78 as my array. This is the formula in CL2 onwards:=IFERROR(INDEX('Hidden Sheet'!A$1:A$4,SMALL(IF('Hidden Sheet'!C$1:C$4=B2,ROW('Hidden Sheet'!C$1:C$4)-ROW('Hidden Sheet'!C$1)+1),ROWS(CL$1:CL1))),"")

CL1 has the following: =INDEX('Hidden Sheet'!A1:F4,MATCH(B2,'Hidden Sheet'!C1:C4,0),1,1).

However, CL1 is returning exactly the same as the entire array of values underneath it. Can't seem to figure out why, either.

[daddylonglegs]

Start with this formula in CL1 (with nothing in the other cells)

=IFERROR(INDEX('Hidden Sheet'!A$1:A$4,SMALL(IF('Hidden Sheet'!C$1:C$4=B2,ROW('Hidden Sheet'!C$1:C$4)-ROW('Hidden Sheet'!C$1)+1),ROWS(CL$1:CL1))),"")

confirm with CTRL+SHIFT+ENTER and only copy down once you have done that

Of course you are only referencing 4 rows of Hidden Sheet, I'm assuming you need to expand that

[Libelnon]

Worked a treat, thanks.