Match one cell data in two worksheets and copy 3 cells data across

roccoau · Dec 2, 2023

Hi
I have data in two worksheets.
In worksheet1 column AF has a list of Purchase orders
In worksheet2 column B also has a list of Purchase orders
I would like to compare both worksheets and when a similar PO number is found, I would like the data in columns A and C in worksheet2 copied across to columns AD & AB in worksheet1, respectively
Sample attached
Sorry, I am only new to Excel, and I would greatly appreciate any help

alansidman · Dec 2, 2023

1. Cannot manipulate data in a picture. Please reload your data using XL2BB. Your sample only needs to be 8-12 records.
2. What version of XL are you using. Please update your profile to reflect that information as it will determine which functions within XL to use to solve your issue.

Fluff · Dec 2, 2023

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Match data in two worksheets and copy 2 columns accross
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

roccoau · Dec 2, 2023

alansidman said:
1. Cannot manipulate data in a picture. Please reload your data using XL2BB. Your sample only needs to be 8-12 records.
2. What version of XL are you using. Please update your profile to reflect that information as it will determine which functions within XL to use to solve your issue.

Hi Thanks for advise on this
Not sure if I am doing it right

Workshhet1

Sample.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

1

Date of transaction

Transaction

Amount (OMR) VAT

Declaration Number

As Per Import Purchase Log (JDE)

06.Imports Bill Of Entry Number

Name Of The Supplier

Location (Country) Of The Supplier - Place Of Supply ,Along With Complete Address

Location (Country) Of The Supplier

Tax Code

Nature Of Supply

Invoice Number

Date Of Invoice

Invoice Value

Rate Of Exchange

Quantity

VAT Amount

Value Of Purchase In OMR

Date Of Imports Of Goods

Bill Of Entry Number

VAT Paid (YES/NO/Suspended)

Value Of Goods As Per Customs

Customs Duty

Excise Tax

Taxable Value Of Goods As Per Customers(CIF+Customs Duty+Excise Tax)

VAT Amount ( Import Log)

Company

GL Date

Invoice Type

Invoice Number

Purchase Order Type

Purchase Order

9

17/07/2023

U-PO000731

10

17/07/2023

U-PO000714

11

17/07/2023

U-PO000731

12

17/07/2023

U-PO000587

13

17/07/2023

U-PO000552

14

18/07/2023

U-PO000288

Worksheet1

Worksheet2

Sample.xlsx

A

B

C

D

1

No.

Order No.

Posting Date

Vendor Invoice No.

2

U-PPI002451

U-PO000764

28/11/2023

23006139

3

U-PPI002450

U-PO000860

28/11/2023

23070020

4

U-PPI002449

U-PO001329

27/11/2023

23-441

5

U-PPI002443

U-PO001308

27/11/2023

2023003577

6

U-PPI002442

U-PO001295

27/11/2023

CINV-230020211

7

U-PPI002441

U-PO001017

27/11/2023

SS-K 5964/2023

8

U-PPI002440

U-PO001243

27/11/2023

SIV-23/1078

9

U-PPI002439

U-PO001278

27/11/2023

UG/UAE/INV/2023/824

10

U-PPI002438

U-PO001278

27/11/2023

UG/UAE/INV/2023/859

11

U-PPI002437

U-PO001197

27/11/2023

7500023157

12

U-PPI002436

U-PO001251

27/11/2023

7500023595

13

U-PPI002435

U-PO001277

27/11/2023

7500023594

14

U-PPI002434

U-PO001277

27/11/2023

7500023501

15

U-PPI002433

U-PO001268

27/11/2023

7500023470

16

U-PPI002432

U-PO001251

27/11/2023

7500023312

17

U-PPI002431

U-PO000867

27/11/2023

CTI/23-24/TI-19

18

U-PPI002430

U-PO000881

27/11/2023

23006146

19

U-PPI002429

U-PO001343

27/11/2023

IN-1749U-02

20

U-PPI002428

U-PO001314

27/11/2023

PI 51464

21

U-PPI002427

U-PO000569

27/11/2023

CTI/23-24/TI-18

22

U-PPI002426

U-PO000857

27/11/2023

INV01234

23

U-PPI002425

U-PO000777

27/11/2023

1810002600

24

U-PPI002424

U-PO000634

27/11/2023

20230442

Worksheet2

MountainFog · Dec 2, 2023

Sounds like this is a simple Lookup.
Try this for Column AB

Excel Formula:

=IF($AF2<>"",XLOOKUP($AF2,Sheet1!$B:$B,Sheet1!$C:$C,"NOT FOUND"),"")

And this for Column AD

Excel Formula:

=IF($AF2<>"",XLOOKUP($AF2,Sheet1!$B:$B,Sheet1!$A:$A,"NOT FOUND"),"")

roccoau · Dec 2, 2023

MountainFog said:
Sounds like this is a simple Lookup.
Try this for Column AB

Excel Formula:

=IF($AF2<>"",XLOOKUP($AF2,Sheet1!$B:$B,Sheet1!$C:$C,"NOT FOUND"),"")

And this for Column AD

Excel Formula:

=IF($AF2<>"",XLOOKUP($AF2,Sheet1!$B:$B,Sheet1!$A:$A,"NOT FOUND"),"")

Thanks for tip
I got it to work with below code

AD =XLOOKUP(@AF:AF,sheet2!B:B,sheet2!A:A,"NOT FOUND")

AB =XLOOKUP(@AF:AF,sheet2!B:B,sheet2!C:C,"NOT FOUND")

roccoau · Dec 8, 2023

Hi I have another problem I would like to find a solution for, if someone can please give advice.
Sheet1 and Sheet2 sometimes have the same purchase order number listed multiple times on each sheet.
When there is only one PO number listed and I do the XLookup from sheet1 cell AF and match the PO number in sheet2 cell B the value in Sheet 2 cell A is returned to Cell AD in Sheet 1
But when there is more than one PO listed in each sheet, the returned data to sheet1 Cell AD from Cell A in Sheet2 is always the same data which is always taken from the first occurrence of the PO no in sheet 2.
Is there a way to list the next lot of data from CELL A in sheet2 for the subsequent same PO number quoted and any other subsequent same PO with the same PO number quoted ?

The current formula is AD =XLOOKUP(@AF:AF,sheet2!B:B,sheet2!A:A,"NOT FOUND") but not sure what to add to accomplish the above if at all possible

Thank for any help

roccoau · Dec 8, 2023

Sample below
Sheet 1

VAT Detals Accounts data template (1).xlsx

AF

9

U-PO000552

10

U-PO000731

11

U-PO000587

sheet1

roccoau · Dec 8, 2023

Sample Sheet 1

VAT Detals Accounts data template (1).xlsx

AD

AE

AF

1

Invoice Number

Purchase Order Type

Purchase Order

2

NOT FOUND

PO

PAST PO HERE (AF)

3

U-PPI001657

PO

U-PO000850

4

U-PPI001714

PO

U-PO000763

5

U-PPI002155

PO

U-PO000731

6

U-PPI001476

PO

U-PO000714

7

U-PPI002155

PO

U-PO000731

8

U-PPI002154

PO

U-PO000587

9

U-PPI002318

PO

U-PO000552

10

U-PPI002155

PO

U-PO000731

11

U-PPI002154

PO

U-PO000587

12

U-PPI002233

PO

U-PO000539

13

NOT FOUND

PO

U-PO000703

14

U-PPI001512

PO

U-PO000606

15

U-PPI001927

PO

U-PO000973

16

NOT FOUND

PO

U-PO000647

17

NOT FOUND

PO

U-PO000552

18

U-PPI002272

PO

U-PO000599

19

U-PPI001866

PO

U-PO000794

20

U-PPI002155

PO

U-PO000731

21

U-PPI002154

PO

U-PO000587

22

U-PPI001927

PO

U-PO000973

23

U-PPI002050

PO

U-PO000798

24

U-PPI002155

PO

U-PO000731

25

U-PPI002154

PO

U-PO000587

26

U-PPI002082

PO

U-PO000627

27

U-PPI002255

PO

U-PO000855

28

U-PPI002102

PO

U-PO000616

29

U-PPI002255

PO

U-PO000855

30

U-PPI002102

PO

U-PO000616

31

U-PPI001654

PO

U-PO000957

32

U-PPI001458

PO

U-PO000607

33

U-PPI001941

PO

U-PO000999

34

U-PPI001512

PO

U-PO000606

sheet1

Cell Formulas
Range	Formula
AD2:AD34	AD2	=XLOOKUP(AF:AF,sheet2!B:B,sheet2!A:A,"NOT FOUND")

SHEET 2

VAT Detals Accounts data template (1).xlsx

A

B

C

D

1

No.

Order No.

Posting Date

Vendor Invoice No.

2

U-PPI002451

U-PO000764

28/11/2023

23006139

3

U-PPI002450

U-PO000860

28/11/2023

23070020

4

U-PPI002449

U-PO001329

27/11/2023

23-441

5

U-PPI002443

U-PO001308

27/11/2023

2023003577

6

U-PPI002442

U-PO001295

27/11/2023

CINV-230020211

7

U-PPI002441

U-PO001017

27/11/2023

SS-K 5964/2023

8

U-PPI002440

U-PO001243

27/11/2023

SIV-23/1078

9

U-PPI002439

U-PO001278

27/11/2023

UG/UAE/INV/2023/824

10

U-PPI002438

U-PO001278

27/11/2023

UG/UAE/INV/2023/859

11

U-PPI002437

U-PO001197

27/11/2023

7500023157

12

U-PPI002436

U-PO001251

27/11/2023

7500023595

13

U-PPI002435

U-PO001277

27/11/2023

7500023594

14

U-PPI002434

U-PO001277

27/11/2023

7500023501

15

U-PPI002433

U-PO001268

27/11/2023

7500023470

16

U-PPI002432

U-PO001251

27/11/2023

7500023312

17

U-PPI002431

U-PO000867

27/11/2023

CTI/23-24/TI-19

18

U-PPI002430

U-PO000855

27/11/2023

23006146

19

U-PPI002429

U-PO000616

27/11/2023

IN-1749U-02

20

U-PPI002428

U-PO000855

27/11/2023

PI 51464

21

U-PPI002427

U-PO000616

27/11/2023

CTI/23-24/TI-18

22

U-PPI002426

U-PO000857

27/11/2023

INV01234

23

U-PPI002425

U-PO000777

27/11/2023

1810002600

24

U-PPI002424

U-PO000634

27/11/2023

20230442

25

U-PPI002423

U-PO001084

27/11/2023

SIN-HE-23-00705

26

U-PPI002422

U-PO001335

27/11/2023

SO073378-1

27

U-PPI002421

U-PO000693

27/11/2023

23005956

28

U-PPI002420

U-PO000610

27/11/2023

QTECH-23-0149

29

U-PPI002419

U-PO001139

27/11/2023

STE/CIN-24122462

30

U-PPI002418

U-PO001327

27/11/2023

UG/UAE/INV/2023/858

31

U-PPI002416

U-PO001140

27/11/2023

20230230

32

U-PPI002417

U-PO001139

20/11/2023

STE/CIN-24122122

33

U-PPI002415

U-PO001296

20/11/2023

DXB035493

34

U-PPI002405

U-PO000344

15/11/2023

23000428/NI

35

U-PPI002404

U-PO001249

15/11/2023

002/CRINV/2023004228

36

U-PPI002403

U-PO001242

15/11/2023

002/CRINV/2023004229

37

U-PPI002402

U-PO001323

14/11/2023

DXB035727

38

U-PPI002401

U-PO001323

14/11/2023

DXB035726

39

U-PPI002400

U-PO001323

14/11/2023

DXB035725

40

U-PPI002399

U-PO001323

14/11/2023

DXB035724

41

U-PPI002398

U-PO001323

14/11/2023

DXB035723

42

U-PPI002397

U-PO001323

14/11/2023

DXB035722

43

U-PPI002396

U-PO001323

14/11/2023

DXB035721

44

U-PPI002395

U-PO001323

14/11/2023

DXB035720

45

U-PPI002394

U-PO001323

14/11/2023

DXB035719

46

U-PPI002393

U-PO001323

14/11/2023

DXB035718

47

U-PPI002392

U-PO001323

14/11/2023

DXB035717

48

U-PPI002391

U-PO001323

14/11/2023

DXB035716

49

U-PPI002390

U-PO001323

14/11/2023

DXB035715

50

U-PPI002389

U-PO001323

14/11/2023

DXB035714

51

U-PPI002388

U-PO001323

14/11/2023

DXB035713

52

U-PPI002387

U-PO001323

14/11/2023

DXB035712

53

U-PPI002386

U-PO001323

14/11/2023

DXB035711

54

U-PPI002385

U-PO001323

14/11/2023

DXB035710

55

U-PPI002384

U-PO001323

14/11/2023

DXB035709

56

U-PPI002383

U-PO001323

14/11/2023

DXB035708

57

U-PPI002382

U-PO001323

14/11/2023

DXB035707

58

U-PPI002381

U-PO001323

14/11/2023

DXB035706

59

U-PPI002380

U-PO001323

14/11/2023

DXB035704

60

U-PPI002379

U-PO001323

14/11/2023

DXB035703

61

U-PPI002378

U-PO001323

14/11/2023

DXB035702

62

U-PPI002377

U-PO001323

14/11/2023

DXB035701

63

U-PPI002376

U-PO001323

14/11/2023

DXB035690

64

U-PPI002375

U-PO001323

14/11/2023

DXB035691

65

U-PPI002374

U-PO001323

14/11/2023

DXB035692

66

U-PPI002373

U-PO001323

14/11/2023

DXB035693

67

U-PPI002372

U-PO001323

14/11/2023

DXB035694

68

U-PPI002371

U-PO001323

14/11/2023

DXB035696

69

U-PPI002370

U-PO001323

14/11/2023

DXB035695

70

U-PPI002369

U-PO001323

14/11/2023

DXB035689

71

U-PPI002368

U-PO001323

14/11/2023

DXB035688

72

U-PPI002367

U-PO001296

14/11/2023

DXB035728

73

U-PPI002366

U-PO001296

14/11/2023

DXB035513

74

U-PPI002365

U-PO001296

14/11/2023

DXB035512

75

U-PPI002364

U-PO001296

14/11/2023

DXB035510

76

U-PPI002363

U-PO001296

14/11/2023

DXB035509

77

U-PPI002362

U-PO001296

14/11/2023

DXB035508

78

U-PPI002361

U-PO001296

14/11/2023

DXB035506

79

U-PPI002360

U-PO001296

14/11/2023

DXB035505

80

U-PPI002359

U-PO001296

14/11/2023

DXB035504

81

U-PPI002358

U-PO001296

14/11/2023

DXB035503

82

U-PPI002357

U-PO001296

14/11/2023

DXB035502

83

U-PPI002356

U-PO001296

14/11/2023

DXB035501

84

U-PPI002355

U-PO001296

14/11/2023

DXB035500

85

U-PPI002354

U-PO001296

14/11/2023

DXB035499

86

U-PPI002353

U-PO001296

14/11/2023

DXB035498

87

U-PPI002352

U-PO001296

14/11/2023

DXB035497

88

U-PPI002351

U-PO001296

14/11/2023

DXB035496

89

U-PPI002350

U-PO001296

14/11/2023

DXB035495

90

U-PPI002349

U-PO001296

14/11/2023

DXB035494

91

U-PPI002348

U-PO001296

14/11/2023

DXB035491

92

U-PPI002347

U-PO001296

14/11/2023

DXB035490

93

U-PPI002346

U-PO001296

14/11/2023

DXB035488

94

U-PPI002342

U-PO001321

14/11/2023

QCP-INV-2023-13870

95

U-PPI002341

U-PO000610

14/11/2023

QTECH-23-0130

96

U-PPI002340

U-PO000638

14/11/2023

12008

97

U-PPI002339

U-PO000638

14/11/2023

11989

98

U-PPI002338

U-PO000764

14/11/2023

23003156

99

U-PPI002337

U-PO000913

14/11/2023

23006005

100

U-PPI002336

U-PO001310

14/11/2023

STE/CIN-24122376

101

U-PPI002335

U-PO001313

14/11/2023

SIV-23/1183

102

U-PPI002334

U-PO001211

14/11/2023

ASO-SAL-230395

103

U-PPI002333

U-PO001288

13/11/2023

2310024

104

U-PPI002332

U-PO001230

10/11/2023

IN-1752-03 (1584U)

105

U-PPI002331

U-PO001230

10/11/2023

IN-1562U-04

106

U-PPI002330

U-PO001162

10/11/2023

173853

107

U-PPI002329

U-PO001200

10/11/2023

237600247

108

U-PPI002328

U-PO001234

10/11/2023

173856

109

U-PPI002327

U-PO001234

10/11/2023

173854

110

U-PPI002326

U-PO001043

10/11/2023

PPIN095496

111

U-PPI002325

U-PO001301

10/11/2023

DQT-170853

112

U-PPI002324

U-PO001231

10/11/2023

IN/181993

113

U-PPI002323

U-PO001219

10/11/2023

IN/181994

114

U-PPI002322

U-PO001271

10/11/2023

6020221

115

U-PPI002321

U-PO001283

10/11/2023

6020286

116

U-PPI002320

U-PO001223

10/11/2023

REL/ES/23-24/037

117

U-PPI002319

U-PO001178

10/11/2023

4630002889

118

U-PPI002318

U-PO000552

10/11/2023

POI005846

119

U-PPI002317

U-PO001252

10/11/2023

ZE2310-1110

120

U-PPI002316

U-PO000717

10/11/2023

POI005847

121

U-PPI002315

U-PO000677

10/11/2023

POI005816

122

U-PPI002314

U-PO001188

10/11/2023

PF-217 GIC/23-24

123

U-PPI002313

U-PO000635

10/11/2023

2023000348

124

U-PPI002312

U-PO000745

10/11/2023

2023001478

125

U-PPI002311

U-PO001072

10/11/2023

2023002749

126

U-PPI002310

U-PO000580

10/11/2023

SS-K 5126/2022

127

U-PPI002309

U-PO000758

10/11/2023

237600242

128

U-PPI002305

U-PO000977

9/11/2023

ARDGB975

129

U-PPI002304

U-PO001101

9/11/2023

ARDGB974

130

U-PPI002303

U-PO000966

9/11/2023

S.INV20231019

131

U-PPI002302

U-PO000698

9/11/2023

RI-0158/2023/COMM/AS/INV-155

132

U-PPI002301

U-PO000702

9/11/2023

RI-0157/2023/COMM/AS/INV-162

133

U-PPI002300

U-PO000681

9/11/2023

RI-0154/2023/COMM/AS/INV-154

134

U-PPI002299

U-PO000854

9/11/2023

RSPL/E/2324/453

135

U-PPI002308

U-PO000839

6/11/2023

237600190

136

U-PPI002295

U-PO000707

6/11/2023

4000576704

137

U-PPI002294

U-PO000881

31/10/2023

23005345

138

U-PPI002293

U-PO000693

31/10/2023

23005346

139

U-PPI002292

U-PO000693

31/10/2023

23005340

140

U-PPI002291

U-PO001143

31/10/2023

23-12989

141

U-PPI002290

U-PO001143

31/10/2023

23-12981

142

U-PPI002289

U-PO000725

31/10/2023

23-12637

143

U-PPI002288

U-PO001145

31/10/2023

INV-0077

144

U-PPI002287

U-PO000171

31/10/2023

RI 23018901

145

U-PPI002286

U-PO000745

31/10/2023

2023002534

146

U-PPI002285

U-PO000751

31/10/2023

2023002256

147

U-PPI002284

U-PO000751

31/10/2023

2023002180

148

U-PPI002283

U-PO000744

31/10/2023

2023002177

149

U-PPI002282

U-PO000744

31/10/2023

2023002255.

150

U-PPI002281

U-PO001019

31/10/2023

2023002255

151

U-PPI002280

U-PO000812

31/10/2023

2023002048

152

U-PPI002279

U-PO000812

31/10/2023

2023002181

153

U-PPI002278

U-PO001165

31/10/2023

2023003260

154

U-PPI002277

U-PO001202

31/10/2023

2023003262

155

U-PPI002276

U-PO001206

31/10/2023

2023003261

156

U-PPI002275

U-PO001168

31/10/2023

2023003013

157

U-PPI002274

U-PO001187

31/10/2023

2023003259

158

U-PPI002273

U-PO001203

31/10/2023

2023003258

159

U-PPI002272

U-PO000599

31/10/2023

INV01053

160

U-PPI002271

U-PO000599

31/10/2023

INV01046

sheet2

Match one cell data in two worksheets and copy 3 cells data across

roccoau

New Member

Attachments

Excel Facts

alansidman

Well-known Member

Fluff

MrExcel MVP, Moderator

roccoau

New Member

MountainFog

Board Regular

roccoau

New Member

roccoau

New Member

roccoau

New Member

roccoau

New Member

Similar threads

Forum statistics

Share this page

Match one cell data in two worksheets and copy 3 cells data across

New Member

Attachments

Excel Facts

Well-known Member

MrExcel MVP, Moderator

New Member

Board Regular

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock