Search code examples
excelexcel-formuladuplicatesworksheet-functionrow-number

Excel Row Number with duplicated values

I have to workbooks (1 sheet each) that I am comparing.

Example of Sheet 1:

PO     Line  AMT
PO1234  1    $5.00 
PO1234  2    $7.00 
PO5678  1    $10.00 
PO5678  2    $25.00

Example of Sheet 2:

PO      Line AMT
PO1234  1    $5.00 
PO1234  2    $7.00 
PO5678  1    $10.00 
PO5678  2    $25.00

I have tried match, and row formulas but only returns the row for the first match.

What I am looking for is row a formula that validates all 3 criteria and returns the valid row number like the attached.

enter image description here

Solution

  • Enter the following formula not using Enter, but instead, Ctrl + Shift + Enter
    =MATCH(1,(A2=Sheet2!A:A)*(B2=Sheet2!B:B)*(C2=Sheet2!C:C),0)

    enter image description here