Nested VLookup in IF statement - with ISERROR

[Robbo679]

Hi I wonder if anyone can guide me please.

I have 3 Sheets that an item moves across depending on its delivery progress, therefore is should only ever be on one Sheet.
I then have a Master Sheet to have an over sight over all items, which will also then be used to gather further data (I will sort that after).

Sheets: Master, New Deliveries, Deliveries Offered, Delivered

[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl1962, width: 67, bgcolor: #4F81BD"]PH001
[/TD]
[TD="class: xl1961, width: 165, bgcolor: transparent"]New Deliveries
[/TD]
[/TR]
[TR]
[TD="class: xl1962, bgcolor: #4F81BD"]PH002
[/TD]
[TD="class: xl1961, bgcolor: transparent"]Deliveries Offered
[/TD]
[/TR]
[TR]
[TD="class: xl1962, bgcolor: #4F81BD"]PH003
[/TD]
[TD="class: xl1961, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl1962, bgcolor: #4F81BD"]PH004
[/TD]
[TD="class: xl1963, bgcolor: transparent"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]

I have a VLookup to see if PH001 is on the 'New Deliveries' Sheet - Simple enough:
=IF(VLOOKUP($A3,'New Deliveries'!$A:$A,1,FALSE)=$A3,"New Deliveries")

However if PH001 has now moved to Deliveries Offered I get the error #N/A
So I have incorporated ISERROR

=IF(ISERROR(VLOOKUP(A4,'New Deliveries'!A:A,1,FALSE)=A4),IF(ISERROR(VLOOKUP(A4,'Deliveries Offered'!A:A,1,FALSE)=A4),"Deliveries Offered"),"New Deliveries")

These leads to the result (as per PH004 above) of FALSE.

Once this is worked out I will then duplicate the solution to incorporate the 'Delivered' Sheet.


Any help or better solution to the issue is really appricieated.

 

[Diego_]

The ISERROR checks if a value is an error and returns TRUE or FALSE. If you get a "FALSE" it means that the value you test is not an error... you would need to check your IF statement to see which one is returning the FALSE and set the return value for false that you want to get.

I'm pretty sure the solution is quite simple, as in putting the desired delivery status for A4 somewhere in your IF statement as return value for FALSE but I'm not good enough as to tell you where to put it wihtout a sample of your data and knowing on which sheet your formula is.

Could you posta a small sample of the data of all sheets? I'll try to simulate it and see i I can figure it out but having a real sample would help.

 

[Diego_]

Might not be the cleanest solution ever but it seems to do be doing the trick.

I put PH001 in new_deliveries, PH002 in deliveries_offered, PH003 in delivered and volountarily omitted to put PH004 in any sheet so you have a return value if the shipment is on none of the 3 other sheets.

You will have to adjust the sheets name to match yours, I have a habit of not putting spaces in sheet names but use the _ instead. This formula is for the Master Sheet, sitting in B1.

Hope this helps.

	A	B
PH001	New Deliveries
PH002	Deliveries Offered
PH003	Delivered
PH004	Shipment Not Found

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

MasterSheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A1,New_Deliveries!A:A,1,FALSE)=A1),IF(ISERROR(VLOOKUP(A1,Deliveries_Offered!A:A,1,FALSE)=A1),IF(ISERROR(VLOOKUP(A1,Delivered!A:A,1,FALSE)=A1),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A2,New_Deliveries!A:A,1,FALSE)=A2),IF(ISERROR(VLOOKUP(A2,Deliveries_Offered!A:A,1,FALSE)=A2),IF(ISERROR(VLOOKUP(A2,Delivered!A:A,1,FALSE)=A2),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A3,New_Deliveries!A:A,1,FALSE)=A3),IF(ISERROR(VLOOKUP(A3,Deliveries_Offered!A:A,1,FALSE)=A3),IF(ISERROR(VLOOKUP(A3,Delivered!A:A,1,FALSE)=A3),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A4,New_Deliveries!A:A,1,FALSE)=A4),IF(ISERROR(VLOOKUP(A4,Deliveries_Offered!A:A,1,FALSE)=A4),IF(ISERROR(VLOOKUP(A4,Delivered!A:A,1,FALSE)=A4),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Robbo679]

Might not be the cleanest solution ever but it seems to do be doing the trick.

I put PH001 in new_deliveries, PH002 in deliveries_offered, PH003 in delivered and volountarily omitted to put PH004 in any sheet so you have a return value if the shipment is on none of the 3 other sheets.

You will have to adjust the sheets name to match yours, I have a habit of not putting spaces in sheet names but use the _ instead. This formula is for the Master Sheet, sitting in B1.

Hope this helps.

	A	B
PH001	New Deliveries
PH002	Deliveries Offered
PH003	Delivered
PH004	Shipment Not Found

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]4
[/TD]

</tbody>

MasterSheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1
[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A1,New_Deliveries!A:A,1,FALSE)=A1),IF(ISERROR(VLOOKUP(A1,Deliveries_Offered!A:A,1,FALSE)=A1),IF(ISERROR(VLOOKUP(A1,Delivered!A:A,1,FALSE)=A1),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")
[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2
[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A2,New_Deliveries!A:A,1,FALSE)=A2),IF(ISERROR(VLOOKUP(A2,Deliveries_Offered!A:A,1,FALSE)=A2),IF(ISERROR(VLOOKUP(A2,Delivered!A:A,1,FALSE)=A2),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")
[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3
[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A3,New_Deliveries!A:A,1,FALSE)=A3),IF(ISERROR(VLOOKUP(A3,Deliveries_Offered!A:A,1,FALSE)=A3),IF(ISERROR(VLOOKUP(A3,Delivered!A:A,1,FALSE)=A3),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")
[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4
[/TH]
[TD="align: left"]=IF(ISERROR(VLOOKUP(A4,New_Deliveries!A:A,1,FALSE)=A4),IF(ISERROR(VLOOKUP(A4,Deliveries_Offered!A:A,1,FALSE)=A4),IF(ISERROR(VLOOKUP(A4,Delivered!A:A,1,FALSE)=A4),"Shipment Not Found","Delivered"),"Deliveries Offered"),"New Deliveries")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I have jsut spent 20 mins trying to work out how to best create a table here to give you a better understanding.....I prefere Excel than trying to use this

You solution works perfectly, I think I was close but missed something. I will compare and learn.

Thank you for help

 

[Diego_]

Glad I could be of assistance

If you need to post data on the forum for samples and stuff, you can use the MrExcel HTML maker --> http://www.mrexcel.com/forum/2545970-post2.html it's very nice.


Have a nice day.