1

sheet 1

     PNR    AMT    
1   H2JUFL  2343
2   W9CGJJ  4397
3   K9IC8T  12770
4   C9NL2Q  17707
5   K4NG2S  10584
6   MYVIVD  5769

sheet 2

     PNR    AMT
1   K9IC8T  12770
2   C9NL2Q  17707
3   MYVIVD  5769
4   H2JUFL  2343
5   K4NG2S  10584
6   W9CGJJ  4397

How do I reconcile the data in the two sheets to see if the data matches or not? First row is the pnr no. and the second row is amount for that pnr. I would like to first match the pnr and if that is true then match the corresponding amount and state "ok" if true and if false show amount of sheet 2 and in case if pnr of sheet 1 is not present in sheet 2 then state no entry

I have tried =IF(ISERROR(VLOOKUP(a1,sheet2!$aD$1:$a$6,1,FALSE)),"no entry",""). This works for pnr. but I am unable to go further.

chancea
  • 5,858
  • 3
  • 29
  • 39
ABHISHEK ARYA
  • 13
  • 3

2 Answers2

0

I assume your data in both sheets start at A2. You have several options:

  1. Perform a multiple lookup (here in the form of a multiple MATCH).

  2. Build a helper column in sheet1 (use =A2&B2 in C2, copy downwards), and MATCH it with a concatenation from each row in sheet2 (use =MATCH(A2&B2,sheet1!$C$2:$C$15,0) in C2, copy downwards). You may use a helper column in sheet 2 with a concatenation, and use it for matching.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
0

You can use the VLOOKUP function to retrieve the associated AMT on Sheet2 and compare them. If the numbers match then OK, otherwise report the different number. In Sheet1!C2 use the following formula.

=IF(B2=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), 1E+99), "OK", IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "No Match"))

Fill down as necessary. Your results should resemble the following.

        VLOOKUP and compare

I have intentionally changed the AMT in Sheet1!B4 and the PNR in Sheet1!A6 to show different scenarios.

The IFERROR function has been used to catch non-matches.