Match 2 values & return one corresponding value from 2 l

chrisc559

New Member
Joined
Jun 18, 2004
Messages
15
Evening all,

Any assitance greatly appreciated :biggrin:

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
ABCDEFGHIJK
1ListOneListTwo
2ClientNameISINSecurityHoldingAmountBookCostClientNameISINSecurityHoldingAmountBookCost
3ClientAGB0033843144Stock1159,199.0000183,100.00ClientAGB0033843144Stock1160,000.0000180,000.00
4ClientAU002133Stock2296,000.0000296,100.00ClientAU002133Stock2296,000.0000296,100.00
5ClientABMG0630Q1033Stock31,199.2500127,200.00ClientABMG0630Q1033Stock31,199.2500127,200.00
6ClientAGB0006061740Stock4111,155.3260160,100.00ClientAGB0006061740Stock4111,155.3260160,100.00
7ClientBGB0033843144Stock124,352.210027,699.98ClientBGB0033843144Stock125,000.000027,699.98
8ClientBBMG0630Q1033Stock3120.000013,100.00ClientBBMG0630Q1033Stock3120.000013,100.00
9ClientBGB0006061740Stock412,444.312018,100.00ClientBGB0006061856Stock712,444.312017,100.00
10ClientCFI0009004204Stock57,847,620.00000.00ClientCFI0009004204Stock57,847,620.00000.00
11ClientDGB0033843144Stock132,537.000036,814.75ClientDGB0033843144Stock132,537.000036,814.75
12ClientDU002133Stock258,000.000058,100.00ClientDU002133Stock258,000.000058,100.00
13ClientDBMG0630Q1033Stock3206.260022,100.00ClientDBMG0630Q1033Stock3206.260022,100.00
14ClientDFI0009004204Stock51,190,476.0000171,221.15ClientDFI0009004204Stock51,190,476.0000171,221.15
15ClientDFI0009620777Stock6300,000.00000.00ClientDFI0009620777Stock6250,000.00000.00
16ClientEFI0009004204Stock519,356,766.0000134,601.54ClientEFI0009004204Stock519,356,766.0000134,601.54
One


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

Thanks in advance :biggrin: :biggrin: :biggrin:
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Jun 18, 2004
Messages
15
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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"
 

Forum statistics

Threads
1,141,757
Messages
5,708,336
Members
421,566
Latest member
7Nabisco

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