Index Match Showing Incorrect Amounts - Formula when there is no information to pull

Shannon Hallberg

Hello! I am stuck on the INDEX MATCH pulling incorrect amounts when I reference another sheet. It will work for some cells, but not others. I am thinking since the parent sheet doesn't have all the line item costs for each store such as "Shelving" or "IT/LP", that could be the issue, but not all stores will have those costs.

There is no "GC" cost line item in the parent sheet for Store 2070 so it's pulling the cost from the Architect amount even though I changed the formula to reflect "GC":

=INDEX({Invoiced to Date}, MATCH([Store Number/Name]@row + ({Vendor List Range 1} = "GC"), {Store Name + Number}), 0)

I also tried wrapping it in =VALUE, but no luck. Is there a part I can input into the formula to reflect $0 if there is no information to pull? Or do I need to add the line item to the parent sheet and have the cost be $0?

The formula below works if there are line items with costs in the parent sheet being referenced:

Thanks in advance for your insight!

Genevieve P.

Hi @Shannon Hallberg

It looks like you actually want an INDEX(COLLECT formula instead of a MATCH, since you're looking for more than one criteria (ex. you're looking to match the Store Number and Name, but also the "Range 1" column to a specific value, either "GC" or "Architect").

Try this:

=INDEX(COLLECT({Invoiced to Date}, {Store Name + Number}, [Store Number/Name]@row, {Vendor List Range 1}, "GC"))

The COLLECT function creates the filter for you. The first column range is what you want to bring back (the invoice amount). Then you list each column and criteria with commas between. Does that make sense?

If there is no match, we can put an IFERROR statement around the whole thing to return 0:

=IFERROR(INDEX(COLLECT({Invoiced to Date}, {Store Name + Number}, [Store Number/Name]@row, {Vendor List Range 1}, "GC")), 0)

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Shannon Hallberg

I'm glad that worked for you!

However I see that in your IFERROR version it looks like that 1 at the end is missing again. Try adding it in before the 0, like so:

... [Store Number/Name]@row), 1), 0)

Bassam Khalil

Hi @Shannon Hallberg

Hope you are fine, if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Genevieve P.

Hi @Shannon Hallberg

It looks like you actually want an INDEX(COLLECT formula instead of a MATCH, since you're looking for more than one criteria (ex. you're looking to match the Store Number and Name, but also the "Range 1" column to a specific value, either "GC" or "Architect").

Try this:

=INDEX(COLLECT({Invoiced to Date}, {Store Name + Number}, [Store Number/Name]@row, {Vendor List Range 1}, "GC"))

The COLLECT function creates the filter for you. The first column range is what you want to bring back (the invoice amount). Then you list each column and criteria with commas between. Does that make sense?

If there is no match, we can put an IFERROR statement around the whole thing to return 0:

=IFERROR(INDEX(COLLECT({Invoiced to Date}, {Store Name + Number}, [Store Number/Name]@row, {Vendor List Range 1}, "GC")), 0)

Let me know if this works for you!

Cheers,

Genevieve

Shannon Hallberg

Hi @Genevieve P. ,

Thank you for your detailed answer! I attempted to input the formula you provided and it came back saying #INCORRECT ARGUMENT SET. I tried adding in the IFERROR and the result was the same. When I tried to change it back, it now says #NO MATCH for everything. Does this mean the parent sheet was changed? If not, it's probably a user error 😀

Do you know if there is an option to do a screen share with SmartSheet support for the corporate account I am using? I think that might be helpful for me 🙂

Thanks again!

Genevieve P.

Hi @Shannon Hallberg

Did you re-create the formula from scratch, selecting each individual cross-sheet range to reference an individual column in the other sheet?

Is it possible that there's an error in your referenced columns? Formulas will create a sort of domino effect - if a column has one cell with an error, then you attempt to search the entire column in a different formula, it will return that one error.

Yes, depending on your plan-type you may be able to share this with Smartsheet support! If you're licensed with a plan that has Phone support the number will show in your Account at the bottom of the "Plan Info" tab.

You could also check in with the System Admin for your organization to see if you are eligible to book Pro Desk sessions (30-minute screen sharing sessions), through this page. You'll need an access code, which your System Admin should have.

Cheers!

Genevieve

Shannon Hallberg

Hi @Genevieve P.

I recreated the formula this time, but I am now getting #UNPARSEABLE

I used this formula:

=INDEX(COLLECT({Invoiced to Date}, {Vendor Type} = "Architect", [Store Number/Name]@row, {Store Name + Numbers}) 0)

I want to pull the "Invoiced to Date" amount if the "Vendor Type" column says "Architect":

Then if my current sheet say the "Store Number/Name" is 2005, I want to pull that from "Store Name + Numbers"

Do you think I pulled those correctly?

Thank you!

Genevieve P.

Hi @Shannon Hallberg

Thank you for all this information, the formula and screen captures, this is exactly what I needed to see!

Using Commas:

So the first thing to note is that in your formula you have:

{Vendor Type} = "Architect"

In a COLLECT function commas are essentially the = sign. You list the range, then comma, then the criteria like so:

{Vendor Type}, "Architect"

Order of Columns/Criteria:

The next thing to note is the order inside the COLLECT function. First you list the cross-sheet range to look through, then you list the criteria it's searching for. So the order should be like so:

{Column to Search}, "Criteria", {Column to Search}, "Criteria"

In your case:

{Vendor Type}, "Architect", {Store Name + Numbers}, [Store Number/Name]@row

Full Formula:

From your screen captures it looks like all the ranges are correctly selected, which is great! This means that you don't have to re-create your formula at all, just add the comma and swap the order within it. Here's the end result:

=INDEX(COLLECT({Invoiced to Date}, {Vendor Type}, "Architect", {Store Name + Numbers}, [Store Number/Name]@row))

Optional: IFERROR

Note that I removed the 0 at the end. That was for the IFERROR statement once you had confirmed that this formula works. If you want to add that back in, wrap it around your entire statement like so:

=IFERROR(INDEX(COLLECT({Invoiced to Date}, {Vendor Type}, "Architect", {Store Name + Numbers}, [Store Number/Name]@row)), 0)

Let me know if this works!

Cheers,

Genevieve

Shannon Hallberg

Hi @Genevieve P.

Thank you for the explanations, I appreciate it!

I gave it a try and I am getting #INCORRECT ARGUMENT SET. I also tried to add IFERROR, but this Store has a cost for Architect. Any suggestions?

=INDEX(COLLECT({Invoiced to Date}, {Vendor Type}, "Architect", {Store Name + Numbers}, [Store Number/Name]@row))

Thank you!

Genevieve P.

Hi @Shannon Hallberg

My apologies! We're missing the 1 for the INDEX part of the formula:

=INDEX(COLLECT({Invoiced to Date}, {Vendor Type}, "Architect", {Store Name + Numbers}, [Store Number/Name]@row), 1)

^ See at the end, put a Comma after the first parenthesis, 1, then the final parenthesis.

Did this work?

Shannon Hallberg

Hi @Genevieve P.

Yes that worked! 😀

Now I am trying the IFERROR formula for lines that have no data and I am getting #INCORRECT ARGUMENT SET. Do you have any recommendations?

Thank you!

Genevieve P.

Hi @Shannon Hallberg

I'm glad that worked for you!

However I see that in your IFERROR version it looks like that 1 at the end is missing again. Try adding it in before the 0, like so:

... [Store Number/Name]@row), 1), 0)

Shannon Hallberg

Hi @Genevieve P.

That worked, thank you so much! I am so happy!

Thank you for your patience and continuing to work with me to find the solution. I really appreciate it!

Genevieve P.

No problem at all!