Index Match Multiple Sheets Showing NO MATCH

Ang Knight · 01/10/23

GTE PDI Offline has a winterization column that I am having our Master GTE Unit Tracker read off of.

This was the formula I was using and it was working great.

=INDEX({GTE - PDI Offline Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline Stock#}, 0))

Jan 1 I moved all of 2022 information to it's own sheet but still need to know if units have been winterized. My new formula looks like this:

=IFERROR(INDEX({GTE - PDI Offline Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline Stock#}, 0)), INDEX({GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, 0)))

Now I am having some units that HAVE been winterized showing up as #NO MATCH

Here's my master showing Stock 263935 as #NO MATCH in the Winterization

But here is my PDI Offline sheet showing it has been winterized.

What am I doing wrong here?

Question 2--We also added a GTE Motorized PDI Offline and when I added that into the formula, I stacked another IFERROR on there and got a #BOOLEAN EXPECTED error?

Jeff Reisman · 01/12/23

@Ang Knight

First thing I would do is test my index/match formulas independently. Copy your Stock # 263935 row back to the original sheet so that it's on both sheets. Do both formulas work?

Double check that your stock numbers are actually the same between the Master sheet and the new 2022 offline winterization sheet. If the same value is stored as a number in one sheet, but as text in the other, you'll get that #NO MATCH even though to the naked eye it looks like they should match. You can test this by adding a temporary helper column into each sheet with an IF formula:

=IF(ISTEXT([Stock #]@row), "Text!", IF(ISNUMBER([Stock #]@row), "Number!", "???"))

Then you'll see at a glance if there are any mismatches.

Ang Knight · 01/12/23

@Jeff Reisman, THANK YOU!

I tried the formula and both sheets the stock numbers are numbers. I have found that there are only a handful that will read #NOMATCH and can't seem to find a common reason why it's happening.

This is what I have found the problem to be, but can't figure out a solution--

The unit is put on the Master BEFORE it makes it's way to the PDI Offline sheet. (The unit is winterized during the PDI process.) The master sheet already contains the INDEX/MATCH formula in the winterization column so it will read a "NOMATCH" immediately but once the stock number is added to the PDI sheet it won't correct itself.

It has been very frustrating over here trying to figure out why this is only happening to some and not all units. I have had to go and remove the column formula so I could manually check that it has been winterized.

Does this make sense? Can you help me?

Jeff Reisman · 01/12/23

https://community.smartsheet.com/discussion/comment/356896#Comment_356896

It stays as #NO MATCH even though the matched value is on PDI sheet? Does it update eventually, such as after reloading the sheet or refreshing it?

Ang Knight · 01/12/23

@Jeff Reisman, correct. The matched value is on the PDI sheet but it reads #NOMATCH on the master. It does not update. But not all of them, just some of them.

And now I am noticing some units are checkmarked that they have been winterized but we don't even have them at our facility yet and they are NOT on the PDI sheet. Somewhere I messed up and cannot for the life of me figure it out. I did update the formula to include our motorized units, so my formula is reading:

=IFERROR(INDEX({GTE - PDI Offline Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline Stock#}, 0)), IFERROR(INDEX({GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, 0)), INDEX({GTE - Motorized PDI Offline Winterized}, MATCH([Stock #]@row, {GTE - Motorized PDI Offline Stock #}))))

I have looked at that so many times to try and find an error and can't seem to find one?

Ang Knight · 01/12/23

@Jeff Reisman, these units are showing as winterized even though they have not been received in yet, which means they have not completed PDI (because they can't do PDI if they aren't physically here) and are not on the PDI offline sheet where this formula is reading from.

Did I stump the smartsheet world? Because, leave it to me to do that. 🤦

Jeff Reisman · 01/12/23

@Ang Knight What's the formula for the Winterization checkbox?

Ang Knight · 01/12/23

@Jeff Reisman

=IFERROR(INDEX({GTE - PDI Offline Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline Stock#}, 0)), IFERROR(INDEX({GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, MATCH([Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, 0)), INDEX({GTE - Motorized PDI Offline Winterized}, MATCH([Stock #]@row, {GTE - Motorized PDI Offline Stock #}))))

Jeff Reisman · 01/12/23

https://community.smartsheet.com/discussion/comment/356915#Comment_356915

So you're using these index/matches to set the Winterization checkbox... interesting approach. If you have duplicate Stock#s in the same sheet, you're going to get unpredictable results. It ends up depending on what row the formula finds first. There are many ways to do this, though. I might use something like:

=IF(OR(COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1)) > 0, COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1)) > 0, COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1)) > 0), 1, 0)

This will check the box if it finds any row with that stock number and a checked Winterization box in any of the offline sheets.

Ang Knight · 01/12/23

@Jeff Reisman, that formula gave a #REF result.

The Master Unit Tracker and PDI Offline are both sheets. The winterization column is being manually checked on the PDI Offline sheet once the unit is winterized. Is there a better formula to use so I can see the units that have been winterized on the master as well? I'm still fairly new to smartsheets.

I run a report from the Master to see what units are ready to ship that have not been winterized yet. Shipping information is not on the PDI offline sheet which is why I need the master to know what units are winterized.

There are conditional formatting rules set up on both sheets to check for duplicate stock numbers. I can say with 100% certainty, these are not on either sheet twice.

Jeff Reisman · 01/12/23

I used the references from the formula you posted, so just check those (right click on the reference and Edit the References) and make sure they're pointing to the right ranges. Check that the parentheses colors line up as well. Per your formula, you have three offline sheets:

GTE - PDI Offline

GTE - PDI Offline 9/29/21 - 12/22/22

GTE - Motorized PDI Offline

You want the Winterization box on the Master Unit Tracker sheet to look for the stock number in the other sheets and copy the row's Winterization checkbox value to the Master Unit Tracker sheet, right?

The logic is:

The Master Unit Tracker Winterization checkbox column is looking for the stock # on offline Sheet B, offline Sheet C, and offline Sheet D. If it finds the stock # on Sheet B, and that row has the winterization box checked, it counts 1. Same thing for Sheets C and D. If it's able to count at least one row between the three sheets that has that stock # and the box checked, it checks the winterization column on Sheet A.

Breaking the formula down into its components:

=IF(

OR(

COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1)) > 0,

COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1)) > 0,

COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1)) > 0

),

1, 0)

Try each of the COUNTIFS on your Master Unit Tracker in a Winterization checkbox and see if you get the correct result for each.

Ang Knight · 01/16/23

@Jeff Reisman, sorry I was out a few days. But yes, you are correct on what I am wanting the workflow to be. I guess I am just a little confused--are you wanting me to combine these three formulas? To look like this

=IF(OR(COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1) > 0)),COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1) > 0)),COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1) > 01, 0)

Because I think I screwed that up. It keeps telling me the formula is wrong. I'm sure I messed up the parentheses.

Jeff Reisman · 01/17/23

Yes, there are a bunch of extra parentheses in there and some incorrect operators. Try this:

=IF(OR(COUNTIFS({GTE - PDI Offline Stock#}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1) > 0, COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1) > 0, COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1) > 0), 1, 0)

Ang Knight · 01/17/23

@Jeff Reisman that shows #INCORRECT ARGUMENT SET. I double checked all references too.

Jeff Reisman · 01/17/23

The formulas work, I have tested them. Check you range sizes, make sure they match within each COUNTIFS.

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

For functions that take two ranges: The range sizes don’t match for the function.
The function is missing an argument.
There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

Ang Knight · 01/17/23

@Jeff Reisman, I think I am just going to go ahead and say this will never work. I created a new column to try the formula in and entered it in several times and keep getting the #INCORRECT ARGUMENT SET.

=IF(OR(COUNTIFS({GTE - PDI Offline Stock}, [Stock #]@row, {GTE - PDI Offline Winterization}, (@cell = 1) > 0, OR(COUNTIFS({GTE - PDI Offline 9/29/21 - 12/22/22 Stock #}, [Stock #]@row, {GTE - PDI Offline 9/29/21 - 12/22/22 Winterization}, (@cell = 1) > 0, OR(COUNTIFS({GTE - Motorized PDI Offline Stock #}, [Stock #]@row, {GTE - Motorized PDI Offline Winterized}, (@cell = 1) > 0), 1, 0))))))

I have just been having to manually update this every day. Its just frustrating because index match was working until one day it just decided not to. And I know that is a lot of parentheses at the end. I have tried it with the only one like you had and it didn't work. Am I supposed to be just typing in the (@cell=1) or do I select a cell? I'm sorry, I promise I can normally pick up this stuff pretty easy, I'm not sure why this stupid winterized stuff is giving me such a hard time. 🤷‍♀️

Index Match Multiple Sheets Showing NO MATCH

Answers

#INCORRECT ARGUMENT SET

Cause

Resolution

Help Article Resources

Categories