VLookUp - #INCORRECT ARGUMENT SET

dhkim18 · 09/20/22

Hello team!

I'm trying to reference all of the columns from one smart sheet (let's call is smartsheet A) to another (smartsheet B). Here is my formula:

=VLOOKUP([Helper Column]@row, {Smartsheet A}, 7, true)

7 is the location of the helper column in both smartsheet A and smartsheet B. All of the columns in both smartsheets are in the same location.

SmartLew · 09/21/22

https://community.smartsheet.com/discussion/comment/344187#Comment_344187

I do agree with Gary here; if a lookup formula is what you need, INDEX/MATCH or INDEX/COLLECT will be more robust and less susceptible to issues with column position changes etc

https://community.smartsheet.com/discussion/comment/344175#Comment_344175

However, Let's just quickly look at what a VLOOKUP achieves to make sure you are understanding it correctly and that it is what you need;

In this scenario I am trying to find the Quantity and Value of each product from another sheet.

This is Sheet 1:

In another sheet, I have a full data set, that looks like this.

So to find the Value for Jeans in my original sheet I use this formula:

I start by inputting my search value, I'm looking to find corresponding values for Product

Then I add my reference table, note that my search value, in this case Product column, is on the far left.

Now I complete my formula. I am looking to find the Quantity, which is the 2nd column, so I add "2" as my column number in the formula. "False" indicated I am looking for an exact match.

This then gives me the below in my 1st Sheet

You will notice that this requires values to already be in the Sheet 1. They do not need to be in the same sort order, but they do need to be on the left hand side in the lookup table.

You would then repeat this for the value column and so on and so forth.

Hopefully this helps you ascertain if vlookup will work for you.

SmartLew · 09/20/22

vlookups work left to right; the 7th column should be the value you are trying to return, with the search value being the left most column.

see the formula descriptor below

VLOOKUP( search_value ,lookup_table ,column_num [ match_type ])

What is it you are trying to achieve? if you can share a little more detail I'm sure there's a simple solution!

dhkim18 · 09/20/22

I'm trying to basically import excel files (creating a new secondarysmartsheet for each import) and later referencing each secondary smartsheet to the main smartsheet file.

SmartLew · 09/20/22

https://community.smartsheet.com/discussion/comment/344163#Comment_344163

ok, and what is it you are trying to reference/ return? I'm assuming you have some sort of master sheet, and based on a value that exists in both sheets, you want to return another value in another column?

Perhaps a couple of screenshots might help explain what you are trying to achieve

dhkim18 · 09/20/22

Unfortunately, due to the nature of my work, I'm not allowed to share screenshots. But essentially all of the columns between the secondary Smartsheets and the master Smartsheets are the same. We are trying to have all of the values in the maser Smartsheet reflect the values in the secondary Smartsheets by referencing a handful of columns from the secondary Smartsheet. We would then change the reference whenever an updated secondary Smartsheet is created.

SmartLew · 09/20/22

and in your master sheet, is there a column with values that will also be found in the newly imported sheets? You need to this for a VLOOKUP or similar function to work. Just trying to gauge if indeed you are actually looking for a vlookup here.

Otherwise you can look at Cell linking as a solution.

dhkim18 · 09/20/22

I also thought about cell linking, but I fear that would take too long since there are so many columns. That is why I am aiming to do VLOOKUP. However, the values in the columns are expected to constantly change, so I don't think they will be found in the newly imported sheets.

Gary Thornton · 09/20/22

You should look to use Index/Match or Index/Collect formulas instead of Vlookups, as they're more flexible and less intensive within Smartsheet.

Helpful videos here: https://youtu.be/1-GDf3_gzTQ or https://youtu.be/WnecLKVCg-o

SmartLew · 09/21/22