Vlookup #no match error only on one element in range...
I am wrecking my brains trying to figure out why my vlookup returns #NO MATCH result for one of my selection options, but all other options are working? What am I missing...can someone please put me out of my misery... 😂
I have checked spelling, spaces....it will probably be something stupid..but I am stumped...
=VLOOKUP(Complexity@row, Complexity$2:Data$5, 2)
Range:
Best Answer
-
Consider telling the VLOOKUP formula what type of match you're looking for. In your formula above you are missing that designation and end your formula with the column reference. Try this:
=VLOOKUP(Complexity@row, Complexity$2:Data$5, 2,false)
false = Exact Match
true = close match
As an aside, I find that Index Match formulas are much more stable than VLOOKUP and do not break if someone decides to reorganize the columns/rows on the source sheet. Here is a great explanation of index match: https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation
Answers
-
Consider telling the VLOOKUP formula what type of match you're looking for. In your formula above you are missing that designation and end your formula with the column reference. Try this:
=VLOOKUP(Complexity@row, Complexity$2:Data$5, 2,false)
false = Exact Match
true = close match
As an aside, I find that Index Match formulas are much more stable than VLOOKUP and do not break if someone decides to reorganize the columns/rows on the source sheet. Here is a great explanation of index match: https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation
-
Hi @Summer
Thanks! that solved it, so obvious but in the moment nothing comes to mind...😂
Much appreciated 😎
-
You're welcome @Marcelle Conradie. I get stuck on the simplest things each time so you're not alone there. Sometimes you just need a second set of eyes.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!