Index, match, and criteria

[morbdetro]

I am trying to put together a production report for the warehouse I work at and i have hit a roadblock. I am able to use Index and Match to look up the batch number and return the first row of that batch from the EQ Type column, but sometimes the first few rows contain no useful data. Is there a way to use a named range or something similar to skip these first few rows and actually return the useful data? I have attached a sample of the report I am working on. any help would be appreciated. Thanks. Production Report.xlsx

[jeffreybrown]

How about doing a custom sort on D1:N22

Batch Identifier >> A - Z
EQ TYPE >> A - Z

Now all the zero's are first ahead of Nothing, No, Blank, Empty, and Skip.

Also, you need to lock your ranges.

[morbdetro]

I am doing that now so I can get what I need. I am hoping to find a way to avoid having to manupilate the data table every time I run the report.

[Haseeb Avarakkan]

Or,

If you can enter all values to EXCLUDE to return results in a range.

Eg: Q2 to down enter the values like NO, EMPTY, SKIP, BLANK, NOTHING etc....

Then in B2, with CTRL+SHIFT+ENTER, not just ENTER

=INDEX(N$2:N$22,MATCH(1,IF(D$2:D$22=A2,IF(ISNA(MATCH(N$2:N$22,Q$2:Q$10,0)),1)),0))

Then fill down.

[morbdetro]

I can make that work. I will have to give that a try when I run next weeks. Thanks.