#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