VLOOKUP "No Match" Error when looking up Today()

J. Ches Wilson

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!

Mark Cronk

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

Mark Cronk

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

J. Ches Wilson

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!

Mark Cronk

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