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

New Member
Joined
Apr 24, 2019
Messages
25
Hi

I have an excel sheet with Data like below

Order DateOrder NoNameDelivery DateDueNotes
05/03/2020111Mr A04/05/202020Partial Delivery
06/06/2020112Mr B06/08/202030bal outstanding
05/03/2020113Mr C30/03/202040Partial 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 DateOrder NoNameDelivery DateDueNotes
05/03/2020111Mr A04/05/202020Partial Delivery
05/06/2020116Mr F06/08/202030
05/03/2020120Mr G30/03/202040


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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
How about
=iferror(index(sheet1!F$2:F$100,match(B2,sheet1!B$2:B$100,0)),"")
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDEF
1Order DateOrder NoNameDelivery DateDueNotes
205/03/2020111Mr A04/05/202020Partial Delivery
305/06/2020116Mr F06/08/202030 
405/03/2020120Mr G30/03/202040 
Sheet2
Cell Formulas
RangeFormula
F2:F4F2=IFERROR(INDEX(Sheet1!F:F,MATCH(Sheet2!B2,Sheet1!B:B,0)),"")
 

Sineadd

New Member
Joined
Apr 24, 2019
Messages
25
Book1
ABCDEF
1Order DateOrder NoNameDelivery DateDueNotes
205/03/2020111Mr A04/05/202020Partial Delivery
305/06/2020116Mr F06/08/202030 
405/03/2020120Mr G30/03/202040 
Sheet2
Cell Formulas
RangeFormula
F2:F4F2=IFERROR(INDEX(Sheet1!F:F,MATCH(Sheet2!B2,Sheet1!B:B,0)),"")

Thank you this worked perfectly
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Glad it worked for you. Thanks for the feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,693
Members
416,041
Latest member
behnejm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top