VLookup crossreference "#INCORRECT ARGUMENT SET"

Options

Hi,

I have been having trouble with VLOOKUP function in Smartsheet's.

I have a sheet that I want to reference the Store# to pull over data such as the address from the other sheet.

I have tried a number of things but the formula I am trying to use currently is:

=VLOOKUP([Store#]@row, {ALL Store Openings (Master) Range 3}, 2, false)

The [Store#] is on the sheet I am entering into the Store Address column. I moved the store address column next to the Store# column on the reference sheet and highlighted both columns.


What am I doing wrong? Do the columns need to be named exact like when you pull reports?


Thank you!

Tags:

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Meagan80

    The structure of the formula is correct. Is it possible that either of the columns that you're referencing in the {ALL Store Openings (Master) Range 3} range contain a formula error?

    Formula errors work sort of like dominoes - if there's an error in the column that you're referencing it will pull through to the other formula.

    Do you have formulas in either the Store Number column or the Address column in the other sheet?


    Can you try using an INDEX(MATCH to see if you get the same results? In this formula you select the two columns separately, so they don't have to be together in the same range.

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))


    Let me know if any of this has helped!

    Cheers,

    Genevieve

  • Meagan80
    Meagan80 ✭✭✭✭
    edited 07/06/22 Answer ✓
    Options

    Never mind. I figured it out. :)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Meagan80

    The structure of the formula is correct. Is it possible that either of the columns that you're referencing in the {ALL Store Openings (Master) Range 3} range contain a formula error?

    Formula errors work sort of like dominoes - if there's an error in the column that you're referencing it will pull through to the other formula.

    Do you have formulas in either the Store Number column or the Address column in the other sheet?


    Can you try using an INDEX(MATCH to see if you get the same results? In this formula you select the two columns separately, so they don't have to be together in the same range.

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))


    Let me know if any of this has helped!

    Cheers,

    Genevieve

  • Meagan80
    Meagan80 ✭✭✭✭
    Options

    Thank you!! It works now!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Oh good! I'm glad to hear it. 🙂

  • 404.IRL
    404.IRL ✭✭
    Options

    Fernando Flores | M365 Enterprise Project Manager ✌️ 🍻

  • Meagan80
    Meagan80 ✭✭✭✭
    Options

    Is there a way to use the Index and Match to search data on multiple sheets?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Meagan80

    How many sheets are you looking to search? It may be easier to create a Report with Filters or Grouping instead of formulas.

    However, yes, you could create multiple INDEX(MATCH statements and have them in an IF statement to say that IF there's no match from the first formula, run the second formula looking at the next sheet,

    Ex:

    =IFERROR(INDEX({Sheet 1 Return Value}, MATCH([Value to match]@row, {Sheet 1 Match Value}, 0)), INDEX({Sheet 2 Return Value}, MATCH([Value to match]@row, {Sheet 2 Match Value}, 0)))

  • Meagan80
    Meagan80 ✭✭✭✭
    edited 11/18/21
    Options

    =IFERROR(INDEX({ALL Store Openings (Master) Range 6}, MATCH([Store ID]@row, {ALL Store Openings (Master) Range 4}, 0)), INDEX({ALL Store Openings (Master) Range 5}, MATCH([Store ID]@row, {Previous Year Store Openings Range 2}, 0)), INDEX({Store Openings/Completion 2021 Range 1}, MATCH([Store ID]@row, {Store Openings/Completion 2021 Range 2}, 0)))


    I am getting an #INCORRECT ARGUMENT SET. Can you see what I entered incorrectly? Thanks so much!


    The problem with Reports for this one is, I need 5 columns of data from 3 different sheets but I have 20 columns that we do not want on those sheets. The source sheets already have almost 400 columns.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Meagan80

    Ah, my apologies! The example above was only for two sheets, using the IFERROR to send the formula to a second sheet if the first one returned an error.

    IFERROR has two sections: the formula, then what to do if the formula returns an error. Since you have three to check, we'll have to add in a second IFERROR statement, like so:

    =IFERROR(formula 1, IFERROR(formula 2, formula 3))

    So in your case:

    =IFERROR(INDEX({ALL Store Openings (Master) Range 6}, MATCH([Store ID]@row, {ALL Store Openings (Master) Range 4}, 0)), IFERROR(INDEX({ALL Store Openings (Master) Range 5}, MATCH([Store ID]@row, {Previous Year Store Openings Range 2}, 0)), INDEX({Store Openings/Completion 2021 Range 1}, MATCH([Store ID]@row, {Store Openings/Completion 2021 Range 2}, 0))))


    Does that make sense?

  • Meagan80
    Meagan80 ✭✭✭✭
    Options

    Oh my goodness. I love you! Thank you so much!!! It works perfect now. :)

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Haha no problem at all! I'm so glad I could help. 🙂

  • Meagan80
    Meagan80 ✭✭✭✭
    edited 07/06/22 Answer ✓
    Options

    Never mind. I figured it out. :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!