Formula help-Lookup

Isabella

Well-known Member
Joined
Nov 7, 2008
Messages
643
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
ABCD
1PfolioQuantityInt RateMaturity
2ZPTCOM154412.3612/02/2016
3ZH2DD554415.368/04/2014
4ZH2DD554415.368/04/2014
5BNHUYR11514.25/04/2015
6GHJYUY65482.81/06/2015
Ledger


Excel Workbook
ABCDE
1PfolioQuantityInt RateMaturityCheck
2ZPTCOM 1,544.0012.3612/02/2016OK
3ZH2DD 5,544.0015.368/04/2014OK
4ZHERTY 5,544.0015.368/04/2014ERROR
5BNHUYR 115.0014.25/04/2015OK
6GHJYUY 6,548.002.81/06/2015OK
Statement
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
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

Board Regular
Joined
Jan 21, 2014
Messages
56
what about removing duplicates.. it may work rather than returning an error
 

Isabella

Well-known Member
Joined
Nov 7, 2008
Messages
643

ADVERTISEMENT

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
 

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
>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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Statement, E2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top