Trying to look up 1 Excel Sheet to see if Column A contains a certain value and if it does it should return the value opposite in column H

[Sineadd]

Hi

I have an excel sheet with Data like below

Order Date	Order No	Name	Delivery Date	Due	Notes
05/03/2020	111	Mr A	04/05/2020	20	Partial Delivery
06/06/2020	112	Mr B	06/08/2020	30	bal outstanding
05/03/2020	113	Mr C	30/03/2020	40	Partial Delivery

Every week I run the same report and need to update the notes section if the order doesnt exist already in previous sheet

So in sheet 2 I have exact same Header row but data may be different.
I want to be able to compare the 2 sheets and if the order number in Sheet 2 Column B matches the order number in Sheet 1 Column A that it then enters the corresponding notes from Sheet 1 into Cell F ( Notes)

Order Date	Order No	Name	Delivery Date	Due	Notes
05/03/2020	111	Mr A	04/05/2020	20	Partial Delivery
05/06/2020	116	Mr F	06/08/2020	30
05/03/2020	120	Mr G	30/03/2020	40

So for the example above it should compare the order umber colums and return the value " Partial Delivery into F1


Any help would be greatly appreciated

 

[Fluff]

How about
=iferror(index(sheet1!F$2:F$100,match(B2,sheet1!B$2:B$100,0)),"")

 

[Yongle]

Book1
	A	B	C	D	E	F
1	Order Date	Order No	Name	Delivery Date	Due	Notes
2	05/03/2020	111	Mr A	04/05/2020	20	Partial Delivery
3	05/06/2020	116	Mr F	06/08/2020	30
4	05/03/2020	120	Mr G	30/03/2020	40
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(INDEX(Sheet1!F:F,MATCH(Sheet2!B2,Sheet1!B:B,0)),"")

 

[Sineadd]

Book1
	A	B	C	D	E	F
1	Order Date	Order No	Name	Delivery Date	Due	Notes
2	05/03/2020	111	Mr A	04/05/2020	20	Partial Delivery
3	05/06/2020	116	Mr F	06/08/2020	30
4	05/03/2020	120	Mr G	30/03/2020	40
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(INDEX(Sheet1!F:F,MATCH(Sheet2!B2,Sheet1!B:B,0)),"")

Thank you this worked perfectly

 

[Yongle]

Glad it worked for you. Thanks for the feedback