Variable Type Mysteries

**npatridge** · 01-27-2015, 06:44 PM

Consider the following:
KYE | KYW | "IF() Formula" | "Test Formula 1" | "Test Formula 2"
KYE
KYE
KYE
KYW
KYW
KYW

``BASED ON THIS RETURNING "Yes" from C1:
=IF(INDIRECT(ADDRESS(ROW(),2))="KYW","Yes","No")

``THIS from D1
=SUMPRODUCT(MAX(($A1:$A7=INDIRECT(ADDRESS(ROW(),2)))*ROW($A1:$A7)))

``SHOULD WORK THE SAME AS THIS from E1
=SUMPRODUCT(MAX(($A1:$A7="KYW")*ROW($A1:$A7)))

But it doesn't!! The formula in D1 returns #VALUE! and E1 returns the desired response (7). If I take out the INDIRECT() in the first formula, it returns 0. I'm not sure what the right way to do this is, can someone shine some light on the subject? I appreciate any help!

Copy of Book1.xlsx

**TMS** · 01-27-2015, 06:51 PM

Your post doesn't make a lot of sense.

It would probably be clearer, and easier for someone to work on, if you uploaded a sample workbook ... then we don't have to build it for ourselves and guess at what you've done.

Regards, TMS

**npatridge** · 01-27-2015, 06:58 PM

Attachment posted. Thanks!

**TMS** · 01-27-2015, 07:10 PM

This formula:

Formula:

Please Login or Register  to view this content.

is not an Array Formula.

Press F2 and Enter to remove the curly brackets and make it a "normal" formula.

In fact, none of them are Array Formulae.

Regards, TMS

**TMS** · 01-28-2015, 04:47 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**npatridge** · 01-28-2015, 04:00 PM

So they are, that was silly. I tried to simplify my table/formula to not confuse things, and I ended up mucking it up!

So, I'll try not to simplify so much this time. I have this formula that errors out on me:

=INDEX( $K$1:$O$5, MATCH( 1, ($K$1:$K$5=$A3)* ($L$1:$L$5=$B3)* ($M$1:$M$5= INDIRECT( CONCATENATE( "$",CHAR(COLUMN()+64), (ROW()-(INDIRECT(CONCATENATE("$C$",ROW())))+1), ) ) ), 0 ), MATCH( $F3, $K$1:$O$1, 0 ))

The Variable Type problem I'm getting seems to happen inconsistently when I F9 my way through the formula. The part that throws it is this:

(ROW()-(INDIRECT(CONCATENATE("$C$",ROW())))+1)

When I try to F9 the whole part, it gives me #VALUE!, but if I F9 the innermost parenthesis and work my way out, it works just fine. Any idea what might cause that and how I can fix it? I'm attaching a new spreadsheet to this post. You'll see the problem cells as they return a #N/A.