VLookUp - #INCORRECT ARGUMENT SET

Options
dhkim18
dhkim18
edited 09/21/22 in Formulas and Functions

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.

Best Answer

  • SmartLew
    SmartLew ✭✭✭✭
    Answer ✓
    Options

    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

    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.

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

Answers

  • SmartLew
    SmartLew ✭✭✭✭
    Options

    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!

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

  • dhkim18
    Options

    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
    SmartLew ✭✭✭✭
    Options

    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

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

  • dhkim18
    Options

    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
    SmartLew ✭✭✭✭
    Options

    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.

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

  • dhkim18
    Options

    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
    Options

    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
    SmartLew ✭✭✭✭
    Answer ✓
    Options

    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

    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.

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!