Index Match not working
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
Best Answer
-
@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))
Answers
-
@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))
-
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!.
-
@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.
-
Lot of 0s and Os lol hopefully you follow that
-
I did follow that. I will include the 0 (zero) at the end from now on. I appreciate the explanation. Have a great day!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!