0

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).
0

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

2
  • Your formula as such not working on Google sheets. Trying to understand the logic of capturing an error in a match and combining it with the arrayformula – luison Apr 13 at 12:50
  • Apologies - a mistake in pasting the answer. Formula has been updated. – Tedinoz Apr 13 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.