Index Match not working

Amy Bedard

Hello, Smart community!

I have something that seems VERY simple that just doesn't want to work. I'm using Index/Match.

The sheet I'm referencing has a Primary Column (QMS) with a numbering system to identify if it is a Risk (R) or Opportunity (O). I have put in 17 rows of "R"s (i.e. R01, R02, etc).

The sheet I'm trying to use Index/Match has the formula:

=INDEX({Title1}, MATCH([Project ID]@row, {QMS})

where I want it to return the title where the R or O number match. It has worked fine as long as the number is R## (R01, R02), but not if it is an O## (O01). With O##, I get a #NO MATCH error. I have checked the my O and 0 are correct. I even changed it to a R18 (since it worked 17 times before) and it works. The number is a Primary column, so there are not any properties that I can find (all Text/Number) with no restrictions.

Any ideas.

Thanks.

Amy

Samuel Mueller

@Amy Bedard try adding the optional condition of 0 to the match formula, like this and let me know if that works

=INDEX({Title1}, MATCH([Project ID]@row, {QMS}, 0))

Samuel Mueller

@Amy Bedard try adding the optional condition of 0 to the match formula, like this and let me know if that works

=INDEX({Title1}, MATCH([Project ID]@row, {QMS}, 0))

Amy Bedard

Yes it did work. Do you know why I didn't need it when I was only working with the "R"s? I would have thought it would have been broken then, too.

THANK YOU!.

Samuel Mueller

@Amy Bedard it is because a match formula without the 0 by default looks up a range by alphabetical order, and since R is after O, if you had O after R then it would not find the O. By adding 0 it ignores the alphabetical order and finds the match no matter what. I would suggest always adding the 0.

Samuel Mueller

Lot of 0s and Os lol hopefully you follow that

Amy Bedard

I did follow that. I will include the 0 (zero) at the end from now on. I appreciate the explanation. Have a great day!