VLookup crossreference "#INCORRECT ARGUMENT SET"

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

Never mind. I figured it out. :)

All comments

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

Thank you!! It works now!

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

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

404.IRL

Wow... Thank you @Genevieve P.

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

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)))

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

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?

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

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