VLOOKUP + OR / IF + Index??

[kh99523]

Hi,

am looking to find help for the following problem.

I have 2 Sheets:

Sheet 1: Columns B, C & D. Column B is the Stock Status (Either "No change" or "Change in Stock"). Column C is Transaction ID 1 (e.g XXX2342) and Column D is Transaction ID 2 (e.g XXX2341).

Sheet 2: Columns D, E. Column D are all Transactions ID´s and Column E determines whether the Transaction ID is "in Stock" or "To be deleted".

Ich need a formula for Cells in Column B on Sheet 1. This formula should check whether one of the two Transaction ID´s (Column C & D) is marked with "To be deleted" in Sheet 2 Column E. If one of the two Transaction ID´s is marked with "To be deleted" the cell should say "Change in Stock" and when neither Transaction ID is marked with "To be deleted" it should say "No Change".

I have tried the formula:
=IF(OR(VLOOKUP(D6;Sheet2!$D$4:$E$90;2;FALSE)="To be deleted";VLOOKUP(C6;Sheet2!$D$4:$E$90;2;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

 

[Fluff]

Hi & welcome to MrExcel
It looks like you have a closing ) in the wrong place
=IF(OR(VLOOKUP(D6;Sheet2!$D$4:$E$90;2;FALSE)="To be deleted";VLOOKUP(C6;Sheet2!$D$4:$E$90;2;FALSE)="To be deleted");"Change in Stock";"No Change")

 

[kh99523]

Hi!
Thank you so much for your help! It has just worked out.

Best,
KO

 

[Fluff]

You're welcome & thanks for the feedback

 

[kh99523]

You're welcome & thanks for the feedback

Okay, there happen to be another question coming up based on the solution.

For some "Stock" products is only one Transaction ID available - Now, Excel shows me #N/A for every product that has only one Transaction ID.

I think this will happen due to the "or" function, right? Can you think of any solution where excel will check both Transaction ID´s and in case only one Transaction ID is available, it will check this one and tells me whether there was "No Change" or "Change in Stock".

If my explanation is not clear just give me feedback and I will try to explain it in more detail.

Thanks and kind regards,
KO

 

[Fluff]

If there is only one ID will it always be in col C?

 

[kh99523]

Yes, it will always be in Column C and Column D says "No car in stock".

 

[Fluff]

Ok try
=IF(OR(IFERROR(VLOOKUP(D6;Sheet2!$D$4:$E$90;2;FALSE);"")="To be deleted";VLOOKUP(C6;Sheet2!$D$4:$E$90;2;FALSE)="To be deleted");"Change in Stock";"No Change")