CODEWITHSUNDEEP
excel
nimz
nimz

Reputation: 3

Index Match returning incorrect values

I am using Index/Match to align some values in one spreadsheet to rates that I analyzed in another. I am using the same formula across multiple workbooks, and in one workbook the formula seems to work while in the other it doesn't. The change in workbooks is already accounted for, so I don't know why the formula structure doesn't seem to carry over.

For reference, here are the two formulas, respectively:

=INDEX('ubersuggest_Board Games.xlsx'!$E$2:$E$976,MATCH(J17,'ubersuggest_Board Games.xlsx'!$A$2:$A$976,0))

=INDEX('ubersuggest_Board Game magazine.xlsx'!$D$2:$D$510,MATCH(J18,'ubersuggest_Board Game magazine.xlsx'!$A$2:$A$283))

Here the formula seems to work, getting the values I want:
Here the formula seems to work, getting the values I want

Formula no longer works: Formula no longer works

Thanks for the help!

Upvotes: 0

Views: 2109

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

It's not returning incorrect values - it's returning exactly what your function says to. The problem is that the two formulas are not the same!

Closely comparing the two formulas, it's clear that you're missing an parameter at the end of the second formula, the MATCH_TYPE.

Match_type Behaviour

1 or omitted :
MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0 :
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.

-1 :
MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

When you're having a problem with a formula, break it up into each smaller section so that you can see where the issue is.

For example, you could put the MATCH section of the formula in one cell, and the INDEX function in the next cell, referring to the value in the other cell. This would have made the issue easier to find.

More Information:

Upvotes: 2

Related Questions