1

Greetings Stack Overflow denizens!

I am having an issue trying to get a formula involving INDIRECT to behave itself in conjunction with INDEX. When i use specific row values, the array works fine, but when i try and use INDIRECT to get current row number i get a #VALUE! error.

For example: I have this in cell R108, and it works as it should:

=IF(OR(ISERROR(INDEX(MODEL,SMALL(IF(OWNERID=N108,ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1))),N108=""),"",INDEX(MODEL,SMALL(IF(OWNERID=N108,ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1)))

N108 refers to a value that is matched to OWNERID, which is a named range on another sheet in the same workbook.

But what i want to do is for the formula to reference the same row that it resides on, so this is what i want to work:

=IF(OR(ISERROR(INDEX(MODEL,SMALL(IF(OWNERID=INDIRECT("N"&ROW()),ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1))),INDIRECT("N"&ROW())=""),"",INDEX(MODEL,SMALL(IF(OWNERID=INDIRECT("N"&ROW()),ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1)))

Just to mention for clarity's sake; on one sheet i have a list of computer information which has two named ranges: MODEL and OWNERID As the formula is an array i am remembering to CTRL + SHIFT + ENTER.

What am i missing that is causing INDIRECT to not provide me the the row number for reference purposes? As far as i can tell, it's simply not liking the fact that its being used as part of a named range lookup.

Thank you in advance!

Regards, Dylan.

Community
  • 1
  • 1
Dshore87
  • 13
  • 3

1 Answers1

1

This is a known behavior of INDIRECT in array context. Since INDIRECT will return a reference and not a value the array context seems not able to get the value from that reference. Maybe this is also caused from the volatile behavior of INDIRECT.

Simple Example:

enter image description here

Formulas:

D1 = {=SUM(IF(A1:A10=INDIRECT("A"&{3,5,7}),B1:B10))}

D3 = {=SUM(IF(A1:A10=N(INDIRECT("A"&{3,5,7})),B1:B10))}

D5 = {=SUM(IF(A1:A10=T(INDIRECT("A"&{3,5,7})),B1:B10))}

As you see if INDIRECT is wrapped within N it works as expected.

But for this we must know whether the values are numeric or textual. If it is textual, for example Z1, Z2, Z3, ... , Z10 in column A, then T must be used instead of N.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Thank you Axel for your response! This has certainly solved my issue, OWNERID is textual and as such the "T" prefix has indeed solved the problem. I was not even aware of the existence of this parameter, So thank you for enlightening me! – Dshore87 Aug 04 '16 at 12:30
  • @Dshore87: You are welcome! But to be clear: `N()` and `T()` are functions not parameters. – Axel Richter Aug 04 '16 at 12:43