# Index items if value in one coulum is zero but in other date (coulum)  have value



## Rsdg (Dec 20, 2022)

Hello and thanks for your help
I have two sheets with different date , i want to return items if in one sheets have value and the other with zero  
And i have some condition like this picture


----------



## Flashbond (Dec 20, 2022)

Are the codes always the same per item number?


----------



## Rsdg (Dec 20, 2022)

Flashbond said:


> Are the codes always the same per item number


No, each item can have different codes


----------



## Flashbond (Dec 20, 2022)

I mean, if 100304625 is 166 in November, is it always 166 in October? Otherwise there would be meaningless since there will be a small probability that each can match with the given codes above.


----------



## Rsdg (Dec 20, 2022)

Flashbond said:


> I mean, if 100304625 is 166 in November, is it always 166 in October? Otherwise there would be meaningless since there will be a small probability that each can match with the given codes above.


Yes, I got it wrong, and it is possible that an item will not exist at all in the next month

In a my data, I am looking for items that did not exist in the previous month or were zero, but have a value in the new month or vice versa


----------



## Flashbond (Dec 20, 2022)

INDEXeptioooon! K5:

```
=IF(COUNTIF($B$1:$F$1,VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$G$5:$I$20,2,0))>0,IF(OR(AND(VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$A$5:$C$20,3,0)=0,VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$G$5:$I$20,3,0)>0),AND(VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$A$5:$C$20,3,0)>0,VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$G$5:$I$20,3,0)=0)),INDEX(A$5:A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),""),"")
```
This is an array formula. Hitting Enter is not enough. Press Ctrl+Shift+Enter after paste. Drag down and left.

Edit: I see your reply now. I added October code check also. It is too late to remove


----------



## Flashbond (Dec 20, 2022)

OK,

I removed October code check. Now it checks code only for November. If it's OK, then OK.

```
=IFERROR(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$G$5:$I$20,2,0))>0,IF(OR(AND(VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$A$5:$C$20,3,0)=0,VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$G$5:$I$20,3,0)>0),AND(VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$A$5:$C$20,3,0)>0,VLOOKUP(INDEX($A$5:$A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),$G$5:$I$20,3,0)=0)),INDEX(A$5:A$20,SMALL(IF($B$5:$B$20=$B$1:$F$1,ROW($A$1:$A$16)),ROWS($A$1:A1))),""),"")
```


----------



## Rsdg (Dec 20, 2022)

Flashbond said:


> OK,
> 
> I removed October code check. Now it checks code only for November. If it's OK, then OK.
> 
> ...


Thank you, it really helped me


----------



## Flashbond (Dec 20, 2022)

Glad it did work 👍 Thanks for the feedback. Maybe a shorter formula is possible but this is what I came up with.


----------

