INDEX/MATCH not working

Paul Newcome

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?

J. Craig Williams

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

Craig

Paul 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.

J. Craig Williams

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 Newcome

Ah ha. Ok. Makes sense. Thanks!

J. Craig Williams

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

Craig

Paul Newcome

The gist was enough. Haha. Thanks.

J. Craig Williams

I thought it might be. You are welcome.

Craig