IF logical test pass return with 1st LOOKUP if true, if false then return with 2nd VLOOKUP

[powerzasty]

Hi folks.

I made as much effort as possible to find an answer to my query, however I failed so I need your help.

I have a spreadsheet with few tabs. The end TAB "Schedule" have to pull the dates from few different places, depend on the status of MRP element.

I've managed to get this to work to 75% but I failed on last bit

So here is the status of the formula:

Run 1st - If status = "Order" - pull the date from ZS80, Colum I - SUCCESS
Run 2nd - If status = "Release order for a stock transfer order" - pull the date from ZP11, Column G - SUCCESS
Run 3rd - If status = "Release order for a stock transfer order" but can't find the record in ZP11 then look at the date on ZPEM, COLUMN C - FAIL
Run 4th - If status = " --- anything in the field --- " - - pull the date from ZPEM, Column C - SUCCESS

Here is the formula:

=IFERROR(IF(F2="Order",VLOOKUP(Schedule!G2,'ZS80'!$A$2:'ZS80'!$I$10000,9,0),IF(F2="Release order for a stock transfer order",VLOOKUP(Schedule!G2,'ZP11'!$B$2:'ZP11'!$G$10000,6,0),IF(F2="Release order for a stock transfer order",VLOOKUP(Schedule!G2,IF({1,0,0,0,0},ZPEM!$G$2:ZPEM!$G$10000,ZPEM!$C$2:ZPEM!$C$10000),5,0),IF(NOT(ISBLANK(F2)),VLOOKUP(Schedule!G2,IF({1,0,0,0,0},ZPEM!$G$2:ZPEM!$G$10000,ZPEM!$C$2:ZPEM!$C$10000),5,0))))),"")


Please help with bit in red because I suppose here is the problem.

- Formula to entered into TAB Schedule, Column P.

Thank you and best regards.

[modytrane]

=IFERROR(IF(F2="Order",VLOOKUP(Schedule!G2,'ZS80'!$A$2:'ZS80'!$I$10000,9,0),IFERROR(IF(F2="Release order for a stock transfer order",VLOOKUP(Schedule!G2,'ZP11'!$B$2:'ZP11'!$G$10000,6,0),IF(NOT(ISBLANK(F2)),VLOOKUP(Schedule!G2,IF({1,0,0,0,0},ZPEM!$G$2:ZPEM!$G$10000,ZPEM!$C$2:ZPEM!$C$10000),5,0))), IF(F2="Release order for a stock transfer order",VLOOKUP(Schedule!G2,IF({1,0,0,0,0},ZPEM!$G$2:ZPEM!$G$10000,ZPEM!$C$2:ZPEM!$C$10000),5,0),""))),"")

Here's the correct formula. It should work.

[modytrane]

Here's another version of same formula. It works.

=IFERROR(IF(F2="Order",VLOOKUP(Schedule!G2,'ZS80'!$A$2:'ZS80'!$I$10000,9,0),IF(F2="Release order for a stock transfer order",IFERROR(VLOOKUP(Schedule!G2,'ZP11'!$B$2:'ZP11'!$G$10000,6,0),VLOOKUP(Schedule!G2,IF({1,0,0,0,0},ZPEM!$G$2:ZPEM!$G$10000,ZPEM!$C$2:ZPEM!$C$10000),5,0)),IF(NOT(ISBLANK(F2)),VLOOKUP(Schedule!G2,IF({1,0,0,0,0},ZPEM!$G$2:ZPEM!$G$10000,ZPEM!$C$2:ZPEM!$C$10000),5,0)))),"")