# Match similar values on another sheet by two values (date + number)

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

## 1 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

Sheet B

• 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 Apr 13, 2021 at 12:50
• Apologies - a mistake in pasting the answer. Formula has been updated. Apr 13, 2021 at 20:08