+ Reply to Thread
Results 1 to 4 of 4

INDEX returning wrong values

  1. #1
    Registered User
    Join Date
    09-30-2020
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2

    INDEX returning wrong values

    I have a spreadsheet with two sheets. On the first sheet, there is a list of policies and the date they are up for review. Using conditional formatting, I have managed to highlight the policies that are due.
    On the second sheet, I am trying to list all of those policies that are due. Below is my formula:

    =IFERROR(INDEX(Policies!A$3:A$130, SMALL(IF(Policies!D$1=Policies!E$3:E$130, ROW(Policies!A$3:A$130)-ROW(Policies!$A$4)+1), ROW(1:1))),"")

    This formula works to a degree. The first policy it retrieves is correct, but every policy after that is off by one row. I put $ before every reference to the cells on the Policies sheet because when I dragged the formula down it incremented all by one which made it invalid. I suspect my problem is something to do with the ROW part of the formula, but I've been trying for days and haven't managed to figure it out. Is anyone able to point me in the right direction?
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,835

    Re: INDEX returning wrong values

    Try:

    =IFERROR(INDEX(Policies!A$3:A$130, SMALL(IF(Policies!D$1=Policies!E$3:E$130, ROW(Policies!A$3:A$130)-ROW(Policies!$A$3)+1), ROWS($B$1:$B1))),"")

    again, array-entered.
    Rory
    I drink, and I know things

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,477

    Re: INDEX returning wrong values

    I guess error came from this:
    ROW(Policies!A$3:A$130)-ROW(Policies!$A$4)+1

    Change $A$4 to $A$3 then it should work.

  4. #4
    Registered User
    Join Date
    09-30-2020
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2

    Re: INDEX returning wrong values

    Quote Originally Posted by rorya View Post
    Try:

    =IFERROR(INDEX(Policies!A$3:A$130, SMALL(IF(Policies!D$1=Policies!E$3:E$130, ROW(Policies!A$3:A$130)-ROW(Policies!$A$3)+1), ROWS($B$1:$B1))),"")

    again, array-entered.
    Perfect, thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lookup returning wrong values
    By darxide23 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-19-2020, 02:42 AM
  2. Index Match returning wrong values
    By CoachK88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2018, 12:48 AM
  3. [SOLVED] Index Match function returning wrong values sometimes
    By Dord25 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-19-2018, 09:18 PM
  4. [SOLVED] Index Returning wrong value in certain cells
    By bobredford in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2017, 09:42 PM
  5. [SOLVED] Index formula returning wrong row refferences
    By JO505 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-26-2013, 05:08 PM
  6. Index Max Match formula returning wrong value
    By bk77 in forum Excel General
    Replies: 4
    Last Post: 03-25-2009, 02:17 PM
  7. [SOLVED] Returning the wrong values...but not in the Immediate window?
    By johnhildreth@citynet.net in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2006, 05:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts