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
Bookmarks