# Match 2 values & return one corresponding value from 2 l

#### chrisc559

##### New Member
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
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....

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.

Hi,

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

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.

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"

Many thanks! Works like one of those good dreams!

Replies
6
Views
375
Replies
3
Views
231
Replies
2
Views
173
Replies
3
Views
147
Replies
1
Views
183

1,218,620
Messages
6,143,515
Members
450,492
Latest member
Rusbus1972

### 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.

### Which adblocker are you using?

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

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