#NOMATCH Error in VLOOHUP when values are an exact match

tom@DTPS

I have two sheets:  Change Oorders and Change Order Pricing.  IN Change Orders, there is a column called Task1 which is a dropdown menu.  The values in the drop down were copied and pasted from Change Order Pricing, "Task" column which is the first column.

I created this formula:

=VLOOKUP([Task1]@row, {Change Order Pricing Range 2}, 3, true)

where Change Order Pricing contains all 4 columns on that sheet)

The result returned is correct for the first item in the dropdown, all others return #NOMATCH even though the values are exact matches.  It was my understanding that by adding "true" to the formula, an exact match isn't required.

Anyone have any advice?

Shaine Greenwood

Hi,

You're right in that adding true (or using no parameter there) will mean that an exact match isn't requiredj; however, using the exact match parameter (false) typically produces better results. Using true causes the Smartsheet formula engine to try and evaluate whether something is close enough, and if it can't find anything that it interprets as close enough, it won't find a match. 

Try using the exact match parameter. More on VLOOKUP here: https://help.smartsheet.com/function/vlookup

Here's a case where someone else ran into issues using true and ended up using false to get a better result: https://community.smartsheet.com/discussion/vlookup-returning-wrong-value-solved

eugene.barnes85276

I am having the same problem but I am using

"false" in the VLookup function. I am trying to match two sheet using a column in the primary sheet (Called Participant Info) with a column called CurrDate with a cell value Today().  In the other sheet (Week Info) there are 2 columns in my reference call WorkOutDay and Period. I call my reference "Period".  When using VLOOKUP(CurrDate1, {Week Info Range Period}, 2, false), it returns a #NO MATCH message.  If I hard code the date in the lookup VLOOKUP("4/12/2019", {Week Info Range Period}, 2, false), I get the desired response. I thought Today() might be returning something other that just the date, so I used =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) but got the same #NO MATCH.  Any help toward getting the correct result would be greatly appreciated.I