VLOOKUP misalignment with column sort

hi,

I have found misalignment of the data sort expectations between VLOOKUP function and Smartsheet's Column sort capability.

It seems that when Column is being sorted, underscore symbol "_" is being put ahead of the letters, while when VLOOKUP is looking for the cell to match, it does expect "_" to be after the letters.

As a result - when sorting column using Sort function -- VLOOKUP first finds "_", and stops searching.

See snapshot attached.

Screen Shot 2018-07-03 at 14.18.25.jpg

Find more posts tagged with

Formulas

Comments

p_bureau

Hi,

I'm not sure this is related to the way your column is sorted.

Could you try this formula instead :

=VLOOKUP([Column3]1,[Primary Column]1:[Primary Column]6,1,false)

The argument "false" returns an exact match. Does this help?

Best Regards,

Paul.

Vladimirs

thanks for your response.

that actually resolved the problem for me.

I did not realise that default in vlookup is "approximate match".

that is different behaviour from excel, which confused me.

thank you!

Sign in to join the conversation!

Quick Links

Trending discussions

Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…
Issue with automation process reporting a date in a special column
In the Smartsheet sheet “CRS RO Master” we have programmed an automation called “Track Changes in dates, confirmation and LHG code” [PICTURE 1] This one should report a date of a change in the columns “CRS GO LIVE DATE” and “PMS Go LIVE DATE” in the column “. CRS Check date changed”. [PICTURE 2] The update of the “Go live…