Match and lookup combined

[NewbeeUK]

Hello everyone,

I am a very new member here and I have been struggling with a function since yesterday that I can seem to break.

I have three columns A (Name), B (Reference number), and C (Amount) that I want to match to columns F (name), G (Reference number), and H (Amount). I have been able to use the match function fine but the complication is when I want to give a threshold on the amount where any difference between values in these two amounts between £0.99 and -£0.99 should be ignored. I have the below formula

=IF(AND(MATCH($A7,$F$4:$F$24775,0),MATCH($B7,$G$4:$G$24775,0),MATCH($C7,$H$4:$H$24775,0)),"Reconciled","Not Reconciled")

The dilema I have is to mark it as reconciled where the difference between columns C (Amount) and H (Amount) is not greater than £1 and not less than -£1

Any help will be greatly appreciated

 

[Glenn Kennedy]

Try:

=IF(AND(MATCH($A7,$F$4:$F$24775,0),MATCH($B7,$G$4:$G$24775,0),IFERROR(MATCH(1,(C7>$H$4:$H$24775-1)*(C7<$H$4:$H$24775+1),0),0)),"Reconciled","Not Reconciled")

 

[Glenn Kennedy]

Should be $C7 not C7...

 

[NewbeeUK]

Should be $C7 not C7...

Hey Glenn,

I can't thank you enough, that has worked. I spent most part of last night trying to resolve this. Much appreciated, thank you

 

[Glenn Kennedy]

No problem!!