INDEX/MATCH not working

Paul NewcomePaul Newcome ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
08/28/18 Edited 12/09/19

Below are two screenshots. The first is a screenshot of my target sheet. The other is a screenshot of my reference sheet.

 

On the reference sheet I have a list along the left. In the column to the right is:

=TODAY()

 

On the target sheet is an identical list with the following formula to the right:

=IF(TODAY() = INDEX({New Sheet Test Range 1}, MATCH([Primary Column]@row, {New Sheet Test Range 1}, 0), 2), "Matched Up", "Not Matching")

 

What this SHOULD do is look on the reference sheet for the value that is in the @row position on my target sheet. If it finds that value and the value in the column to the right of that equals TODAY(), then it would say Matched Up. If the date did not equal TODAY(), it would say Not Matching. 

 

Theoretically... They should ALL say "Matched Up" on my target sheet as the reference list is exactly identical and the dates on the reference sheet are populated by =TODAY(). Theoretically...

 

What is actually happening is that half way down my list it suddenly finds that the dates don't match, and in the last row, I have an #INVALID DATA error.

 

I have copy/pasted, manually typed, and drag filled hoping that maybe it was a typo I had made somewhere or something, but I always get the same result. I have also tried different date variations making sure they matched up hoping to force a match to no avail. It is always in the same positions that it changes too. Halfway down the list it turns to mo match and then the last row is #INVALID VALUE. It doesn't matter if its 26 rows or 100 rows. I've even tried creating rand new sheets. Nothing is working.

 

Any suggestions?

Capture.PNG

Capture2.PNG

thinkspi.com

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Short answer: Your range {New Sheet Test Range 1} is two columns. You need two ranges of one column each.

    Craig

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I don't understand how that would affect only half of the results though? No matter what order the target sheet is in, it only recognizes the top half of the reference.

    Capture3.PNG

    thinkspi.com

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Longer answer:

    I don't have time to explain right now, but maybe after dinner.

    In the mean time: 

    The MATCH function is expecting a single range, not a range of columns and rows, that is, to it, the range is not 2 x 26. It is 1 x 52.

    (see "row measure vs column measure")

    Craig

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ah ha. Ok. Makes sense. Thanks!

    thinkspi.com

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Glad I could help. Did you need the longer answer or was the gist enough?

    Craig

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    The gist was enough. Haha. Thanks.

    thinkspi.com

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    I thought it might be. You are welcome.

    Craig

Sign In or Register to comment.