Vlookup #UNPARSEABLE Error linking to separate sheet

I get an #UNPARESABLE error message for a vlookup I am trying to create to a separate file in SmartSheet, I suspect there is a problem in the syntax. I've tried multiple tweaks to this formula with no success.

As a check I exported the SmartSheet and the external file to Excel and had no problem successfully creating a vlookup there. Can anyone see what the error is in my vlookup syntax?

SmartSheet formula

=VLOOKUP(Name11:Name13, {PSF Range}),6,true)

Name is the column with the search value

PSF Range is a named range in a different SmartSheet file which contains the lookup table.

Excel formula

=VLOOKUP(A12:A34,'C:\Users\warrenc2\Documents\[Copy of Export_psfeesV1.xlsx]2019 Forecast by Quarter'!$A:$F,6,TRUE)

"Copy of Export_psfeesv1" is the separate sheet with the lookup table

Thanks

Find more posts tagged with

Healthcare

Finance

Formulas

Comments

Paul Newcome

The first thing I notice is that you are using a range for a search value instead of something specific. The second thing would be an extra closing parenthesis after your cross sheet reference.

warren.cranmer90476

Thanks Paul. I removed the extra closing parenthesis and made the search value a specific reference e.g. "Name 11". This returned a "No Match" although the search value is "Cook" and I validated the same name is in the lookup sheet. I changed the match type to False and it works now.

Paul Newcome

Using "TRUE" for the match type requires an exact match. If your search value is "Cook"

but "cook", "COOK", "John Cook", or any other variation that is not an exact match will return a NO MATCH error.

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…