If Formula (Vlookup/OR/AND)

[kh99523]

Hi,

I have posted a problem earlier which got solved by user "Fluff". Now my supervisor found something in the solution that is not correct due to linking in other formula. Therefore, I am facing a new situation:

I have 2 Sheets:

Sheet 1: Columns B, C, D & E. Column B is the Stock Status (Either "No change" or "Change in Stock"). Column C is the Config-No which belongs to two different Orders - Column D is Ord 1 and Column E is Ord 2. In some cases the cells for Ord 1 and Ord 2 can be empty due the fact that there is only one or no order or due to the fact that one of the order has already been sold

Sheet 2: Columns B, C, D & E. Column B are all Config No. Column C are all Order No. Column D are all Transactions ID´s and Column E determines whether the Config No is "in Stock" or "To be deleted".

Ich need a formula for Cells in Column B on Sheet 1. This formula should check whether the Config ID in Sheet 2 (Column C) is marked with "To be deleted" in Sheet 2 Column E. The issue is that every Config ID No in column c on sheet 2 is listed twice as to each Config No can belong up to two Ord. No. In case one of the is marked with "To be deleted" the formula on Sheet 1 in Column B should say "Change in Stock" and in case both (or one if it is only one order no) are marked with "In Stock" it should stay "No change".


I have tried the formula:
IF(OR(AND(VLOOKUP([C4;Sheet2!$B$4:$E$550;4;FALSE);VLOOKUP([D4;Sheet2!$B$4:$E$550;4;FALSE)="To be deleted");AND(VLOOKUP([C4;Sheet2!$B$4:$E$550;4;FALSE);VLOOKUP([E4;Sheet2!$B$4:$E$550;4;FALSE)="To be deleted"));"Change in Stock";"No Change")

But this did not work..

Anyone here who can help? I wish I could attach a file but I did not manage to find a way to attach an Excel file

Kind regards,
KO

 

[kh99523]

In case I need to explain anything in further detail please ask!

Any help is really much appreciated! Thanks in advance

 

[kh99523]

Solution:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">=IFERROR(IF(INDEX(TRANS!$F$4:$F$222,MATCH(1,(TRANS!$C$4:$C$222=[@[Config No.]])*(TRANS!$F$4:$F$222="To be deleted"),0))="To be deleted","Change in Stock"),"No Change")</code>

 

[DanteAmor]

Try this:

=IF(SUMPRODUCT((sheet2!$B$4:$B$500=C4)*(sheet2!$E$4:$E$500="To be deleted"))= 1,"Change in Stock","No change")

 

[DanteAmor]

How about:

=IF(COUNTIFS(sheet2!$B$4:$B$500,C4,sheet2!$E$4:$E$500,"To be deleted"),"Change in Stock","No change")