VLOOKUP via another Smartsheet not working..

Hi All,

Noob here.. Im trying to perform a VLOOKUP via a second smartsheet, but I keep geting the error "#INCORRECT ARGUEMENT SET", I cant see what I'm doing wrong, can you help please?

This is smartsheet 1 and my formula that I want to populate from smartsheet 2:

Screen Shot 2023-08-08 at 8.41.31 pm.png

This is smartsheet 2, where I want to pull data from:

Screen Shot 2023-08-08 at 8.40.59 pm.png

This is the error I get in smartsheet 1:

Screen Shot 2023-08-08 at 8.41.40 pm.png

Thanks for any tips!

Find more posts tagged with

Accepted answers

All comments

Nick Korna

Hi @neiko,

What is your cross sheet reference looking at? I think it might be only selecting a single column, which is what causes the error as your VLOOKUP is trying to get the value in a non-existent (to it) second column in a range.

Paul Newcome

Give an INDEX/MATCH a try. It is much more resilient than the VLOOKUP function.

=IFERROR(INDEX({Description Column}, MATCH(Ref@row, {Ref Column}, 0)), "")

neiko

https://community.smartsheet.com/discussion/comment/389437#Comment_389437

Hi Nick, I think its looking at the second column in the "Intake" smartsheet, so the "Description" column. I say I think as when im building the formula, I click on "Reference Another Sheet", then from the "Intake" sheet I select the second column (which is Range 2 apparently). Not sure if Im correct doing this, see pics below:

Screen Shot 2023-08-08 at 11.42.07 pm.png

Above is me selecting "Reference Another Sheet" and below me electing the second column namesd "Description":

Screen Shot 2023-08-08 at 11.42.42 pm.png

This ends up with the following formula:

=VLOOKUP(REF@row, {Intake Range 1}, 2, false)

neiko

https://community.smartsheet.com/discussion/comment/389451#Comment_389451

Hi Paul, thanks for this! I'll try it also. For my learning I'll try to get both working with you guys helping me. Appreciate the tip!

Nick Korna

When you select the range it should be highlighting multiple columns, not just one - this is what the issue is.

Your cross sheet reference should look something like this:

You can shift+click on the headers to get a range, or ctrl+click on the headers to get a selection of columns (or combine the two to get a range and then take one/some out!).

neiko

https://community.smartsheet.com/discussion/comment/389451#Comment_389451

Damn! Worked! Thank you! 🙏

=IFERROR(INDEX({Intake Range 1}, MATCH(REF@row, {Intake Range 2}, 0)), "")

Interestd to find out why the VLOOKUP didnt work. Thanks a lot for this Paul 😁

Paul Newcome

Happy to help. 👍️

The VLOOKUP wasn't working because you were not selecting the appropriate range for that particular function. The range in a VLOOKUP needs to be the column to match on (as the leftmost column in the reference table) through the column to pull from (and every column in between).

neiko

https://community.smartsheet.com/discussion/comment/389476#Comment_389476

That worked also! You guys are legends!! Thank you!!

neiko

https://community.smartsheet.com/discussion/comment/389481#Comment_389481

Thats it! Ive only started with formulas yesterday, you have made my evening gents, thanks again 😁

Nick Korna

No problem, glad everything is sorted for you now! 😊

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.