VLOOKUP "No Match" Error when looking up Today()
I am trying to figure out what I am missing on a single sheet I am working with.
I have very simple tables where I am trying to pull the correct Annual Base Rent based off of today's date. This one works, no problem.
On a separate table I get a No Match Error
For some reason, I can fix this error by adding an extra row with continuing dates.
I have replicated this issues with some other date ranges, and it seems to have issues when today falls in the last row. Is there something about the way that Smartsheet handles VLOOKUP that would affect the output if the target row is the last row? The function works normally in Excel.
I have found that workaround, but I wanted to see if I'm missing something obvious. I would prefer to not have to add a helper row for each table. Thanks!
Best Answer
-
Hi @J. Ches Wilson ,
Not sure why the vlookup isn't working. TRUE should cause it to match the value that is less than or equal to search_value. Your range is sorted and has a value less than or equal to the search_value.
Have you tried converting your formula to an INDEX-MATCH? If you use a search type of 1 in your Match it will find the largest value less than or equal to search_value, when your range is sorted.
Help at all?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @J. Ches Wilson ,
Not sure why the vlookup isn't working. TRUE should cause it to match the value that is less than or equal to search_value. Your range is sorted and has a value less than or equal to the search_value.
Have you tried converting your formula to an INDEX-MATCH? If you use a search type of 1 in your Match it will find the largest value less than or equal to search_value, when your range is sorted.
Help at all?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I am able to use the INDEX-MATCH to make it work without the extra row, so thank you for the suggestion. I still can't figure out why the VLOOKUP wasn't working, though. I can move on with my project, but this will continue to eat at my brain. Thanks!
-
Glad you found a solution. Let me know if you figure out the vlookup issue. I remain curious. Thank you for using the Community.
Be Well,
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!