Formula help-Lookup

[Isabella]

Hi

I am after a lookup formula that will return Error or Ok in Col E in sheet statement when comparing data between sheet statement and ledger.

Lookup values

pfolio, Quantity, Int rate, Maturity

Excel Workbook
	A	B	C	D
1	Pfolio	Quantity	Int Rate	Maturity
2	ZPTCOM	1544	12.36	12/02/2016
3	ZH2DD	5544	15.36	8/04/2014
4	ZH2DD	5544	15.36	8/04/2014
5	BNHUYR	115	14.2	5/04/2015
6	GHJYUY	6548	2.8	1/06/2015
Ledger

Excel Workbook
	A	B	C	D	E
1	Pfolio	Quantity	Int Rate	Maturity	Check
2	ZPTCOM	1,544.00	12.36	12/02/2016	OK
3	ZH2DD	5,544.00	15.36	8/04/2014	OK
4	ZHERTY	5,544.00	15.36	8/04/2014	ERROR
5	BNHUYR	115.00	14.2	5/04/2015	OK
6	GHJYUY	6,548.00	2.8	1/06/2015	OK
Statement

 

[PaddyD]

On a phone so schematic answer only:

- in both tables, create an extra column that concatenates each row's values into a single 'unique key'
- do a simple if(isnumber(match... to check for matches

 

[DILIPandey]

Hi Isabella,

Use below formula in Cell E1 of Statement sheet and drag down:-

=IF(SUMPRODUCT((Ledger!$A$1:$A$6=Statement!$A2)*(Ledger!$B$1:$B$6=Statement!$B2)*(Ledger!$C$1:$C$6=Statement!$C2)*(Ledger!$D$1:$D$6=Statement!$D2))>0,"OK","ERROR")

Enter this formula using key combination : ctrl shift enter

Regards,
DILIPandey

 

[Memon]

what about removing duplicates.. it may work rather than returning an error

 

[Isabella]

Why >0?

Hi Isabella,

Use below formula in Cell E1 of Statement sheet and drag down:-

=IF(SUMPRODUCT((Ledger!$A$1:$A$6=Statement!$A2)*(Ledger!$B$1:$B$6=Statement!$B2)*(Ledger!$C$1:$C$6=Statement!$C2)*(Ledger!$D$1:$D$6=Statement!$D2))>0,"OK","ERROR")

Enter this formula using key combination : ctrl shift enter

Regards,
DILIPandey

 

[Isabella]

what about removing duplicates.. it may work rather than returning an error

No i do not want to remove, just want to know which row is incorrect, so i can fix it

 

[DILIPandey]

>0 means (or to check) that there is at least 1 or more than 1 matches found in the Ledger sheet



Regards,
DILIPandey

 

[Aladin Akyurek]

Statement, E2, control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH(1,IF(Ledger!$A$2:$A$6=$A2,
  IF(Ledger!$B$2:$B$6=$B2,IF(Ledger!$C$2:$C$6=$C2,
  IF(Ledger!$D$2:$D$6=$D2,1)))),0)),"OK","ERROR")