Is there a limit to how many rows a function can search, for an IF statement? Having data not show.

MATF12 · 09/26/23

For a requisition database, I maintain a pool sheet of available equipment, as well as sheets for orders and the vendor. I have formulas in a different sheet to add the VINs (let's say 5) that I am planning to add and it checks across the other sheets if that VIN exists. On this "check" sheet, I've noticed some VINs will populate the data as expected and others won't. However, when I manually go and check the other sheets, those VINs and the data that should be coming through exist, but it's not reflecting in my "check" sheet. Any ideas why this is happening? This is not the first time either. I've exported data before and information was not reflecting on the Excel, even though the sheet the data was being pulled from, did reflect accurately. I let the data flow if I'm adding a lot to give it time to work, but if I add 20 rows and 19 of them populate, and the 1 that isn't is in the table in between those 19, makes me wonder if there's another issue.

Darren Mullen · 09/26/23

@MATF12 What's your formula look like?

MATF12 · 09/28/23

https://community.smartsheet.com/discussion/comment/397112#Comment_397112

Afternoon Darren,

Formula is:

=IFERROR(INDEX({Dycom Pool Order Tracker Range 3}, MATCH(VIN@row, {Dycom Pool Order Tracker Range 2}, 0)), 0)

Though not sure why formula would be issue, it's set for the whole column. The data for the rows it did show, is the same data type, and the same status. Ex: row 1 showing status "Requisitioned", row 2 showing "0", but on sheet information is being pulled for, both rows show status "Requisitioned." I then checked VIN to make sure it's correctly identifying it, and both VINs were the only ones in that column. The above formulas is used to pull information into 1 column, I have 2 other similar functions with just the different column swapped in. Since the VIN is the key to getting data, that's why I checked VIN. But it's correct on both, so no idea why data doesn't flow. It's the same data as the other rows that were displaying correctly.

Paul Newcome · 09/29/23

What error message do you get when you remove the IFERROR portion?

=INDEX({Dycom Pool Order Tracker Range 3}, MATCH(VIN@row, {Dycom Pool Order Tracker Range 2}, 0))

Do you have some VINs that are all numbers and other VINs that have letters mixed in?

MATF12 · 09/29/23

https://community.smartsheet.com/discussion/comment/397626#Comment_397626

It's the hash not available or blank, and all VINs are mixed.

Paul Newcome · 10/02/23

I'm not sure I have seen the "not available" error message. Are you able to provide a screenshot of that?

And to clarify "all VINs are mixed"... Do you mean that all VINs are a combination of letters and numbers so that you have no VINs that are just all numbers?

MATF12 · 10/05/23

Apologies, the NA one is my IF statement, forgot I myself write those in. It's the #NO MATCH message. And correct, all VINs for this equipment is a combination of letters and numbers only.

Paul Newcome · 10/05/23

Ok. Understood.

And do you have screenshots that will show a row that SHOULD have a match but is throwing the error along with the corresponding match in the other sheet?

Is there a limit to how many rows a function can search, for an IF statement? Having data not show.

Answers

Help Article Resources

Categories