VLookup crossreference "#INCORRECT ARGUMENT SET"

Meagan80

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!

Genevieve P.

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

Never mind. I figured it out. :)

Genevieve P.

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

Thank you!! It works now!

Genevieve P.

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

404.IRL

https://community.smartsheet.com/discussion/comment/309799#Comment_309799

Wow... Thank you @Genevieve P.

Meagan80

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

Genevieve P.

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

=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.

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

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

Genevieve P.

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

Meagan80

Never mind. I figured it out. :)