Match 2 values & return one corresponding value from 2 l

[chrisc559]

Evening all,

Any assitance greatly appreciated

I have 2 worksheets (List One and List Two) as below. I want to be able to compare the List One holding amount for each client, for each stock with the corresponding List Two data. If the 2 match, then OK is returned next to the row in question, if not then something like Error.

As a further explation (its late!), on List One

Client A holds 159,199 units in Stock 1, but

on List Two

Client A holds 160,000 units in Stock 1

which is wrong. I've been trying to work out some formula to this, but with no joy. The closest I've come is the Look up one value and return one corresponding value

I guess I want look up 2 values and return one coresponding value.

Book4.xls
	A	B	C	D	E	F	G	H	I	J	K
1	ListOne						ListTwo
2	ClientName	ISIN	Security	HoldingAmount	BookCost		ClientName	ISIN	Security	HoldingAmount	BookCost
3	ClientA	GB0033843144	Stock1	159,199.0000	183,100.00		ClientA	GB0033843144	Stock1	160,000.0000	180,000.00
4	ClientA	U002133	Stock2	296,000.0000	296,100.00		ClientA	U002133	Stock2	296,000.0000	296,100.00
5	ClientA	BMG0630Q1033	Stock3	1,199.2500	127,200.00		ClientA	BMG0630Q1033	Stock3	1,199.2500	127,200.00
6	ClientA	GB0006061740	Stock4	111,155.3260	160,100.00		ClientA	GB0006061740	Stock4	111,155.3260	160,100.00
7	ClientB	GB0033843144	Stock1	24,352.2100	27,699.98		ClientB	GB0033843144	Stock1	25,000.0000	27,699.98
8	ClientB	BMG0630Q1033	Stock3	120.0000	13,100.00		ClientB	BMG0630Q1033	Stock3	120.0000	13,100.00
9	ClientB	GB0006061740	Stock4	12,444.3120	18,100.00		ClientB	GB0006061856	Stock7	12,444.3120	17,100.00
10	ClientC	FI0009004204	Stock5	7,847,620.0000	0.00		ClientC	FI0009004204	Stock5	7,847,620.0000	0.00
11	ClientD	GB0033843144	Stock1	32,537.0000	36,814.75		ClientD	GB0033843144	Stock1	32,537.0000	36,814.75
12	ClientD	U002133	Stock2	58,000.0000	58,100.00		ClientD	U002133	Stock2	58,000.0000	58,100.00
13	ClientD	BMG0630Q1033	Stock3	206.2600	22,100.00		ClientD	BMG0630Q1033	Stock3	206.2600	22,100.00
14	ClientD	FI0009004204	Stock5	1,190,476.0000	171,221.15		ClientD	FI0009004204	Stock5	1,190,476.0000	171,221.15
15	ClientD	FI0009620777	Stock6	300,000.0000	0.00		ClientD	FI0009620777	Stock6	250,000.0000	0.00
16	ClientE	FI0009004204	Stock5	19,356,766.0000	134,601.54		ClientE	FI0009004204	Stock5	19,356,766.0000	134,601.54
One

Forgot to say the only 2 unique fields in each list are ClientName and ISIN....

Thanks in advance

 

[NBVC]

Ae the lists exactly the same size and the clients are in corresponding fields?

If so, then =If(D3=J3,"Match","No Match")

add sheet name in front of cell ref for tables in separate sheets.

 

[chrisc559]

Hi,

Thanks for the reply.

Unfortunately, the lists are not the same. Some stock lines are always missing. It is just as quick to check by hand than get the two lists to match exactly (there are about 1500 lines).

I thought maybe something using vlookup, the a nested lookup or something like that...

Any other ideas?

Thanks again

 

[NBVC]

Then try:


=IF(INDEX($J$3:$J$1000,MATCH($A3&"@"&$B3,$G$3:$G$1000&"@"&$H$3:$H$1000,0))<>$D3,"Match","No Match")

adjust ranges to suit and add sheet names as appropriate and then confirm with CTRL+SHIFT+ENTER not just ENTER. Then copy down.

You will need to do the same if checking the second list to the first re-arranging sheet names, references and ranges.

 

[Aladin Akyurek]

L3:

=ISNUMBER(MATCH(1,IF($G$3:$G$16=A2,IF($H$3:$H$16=B3,1)),0))+0

which you need to confirm with control+shift+enter (not just with enter) and copy down.

Select L3:L16 and custom format the range as:

[=0]"Error";[=1]"OK"

 

[chrisc559]

Many thanks! Works like one of those good dreams!