Google-sheets – Match similar values on another sheet by two values (date + number)

google sheets

I need to match two bank statements to check matching values on two different sheets with different number of columns. The idea is so I can import them with their own format but be able to determine with a conditional value or column if there is a matching value which coincides by date + value on sheet B.

I've done some trys with MATCH() but can't figure out using two separate values. Also tried with queries but this complicates the posibilities of editing additional columns on sheet A.

Sample:

Sheet A

╔═════════╦════════╦══════╦═══════╗
║ Date    ║ Descrp ║ Other║ Value ║
╠═══======╬════════╬══════╬══====═╣
║ 01/02/21║ Text   ║ 144  ║ 150   ║
║ 03/02/21║ Text   ║ 144  ║ 127   ║ * (1)
║ 07/02/21║ Text   ║ 144  ║ 93    ║

Sheet B

╔═════════╦════════╦═══════╦═══════╗
║ Date    ║ Other  ║Descrp ║ Value ║
╠═══======╬════════╬══════=╬══====═╣
║ 03/02/21║        ║ Text  ║ 127   ║
║ 09/02/21║        ║ Text  ║ 333   ║
║ 12/02/21║        ║ Text  ║ 23    ║

I would need to see a match on second value of sheet A as it coincides with the first value (date + value) on sheet B.

Also to resolve ideally:

  • number of matches: so 1 would mean one only X in case of more than one match
  • soft match (?) or say 99 in case there is a match of value but on the previous or following date, say… row 1 of sheet B is (02/02/21).

Best Answer

Try this formula for the match of date and Total on SheetA from SheetB.

Enter the formula in cell E2 on Sheet A

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&D2:D, {SheetB!A2:A&SheetB!D2:D,SheetB!B2:C,SheetB!D2:D}, 4, 0 ), ""))

If there is a match, the cell will display the $value.

Sheet A

SheetA

Sheet B

enter image description here

Related Topic