index/match formula? comparing 4 columns

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
hi,

so the workbook is set up like this, with 4 columns:

Column A - country code
Column B - country name for that code

and then you have the same, but not in the same order
Column C - country code
Column D - country name

i need a formula looks at the country code in C2. If C2 is present in column A, formula then checks to see if the respective country name, in D2, is also what is in column B for that particular country code.

is that clear, or more clarification needed?

thanks in advance
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:117.86px;" /><col style="width:121.66px;" /><col style="width:117.86px;" /><col style="width:121.66px;" /><col style="width:184.4px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">COUNTRY CODE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">COUNTRY NAME</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">COUNTRY CODE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">COUNTRY NAME</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">12345</td><td >BRAZIL</td><td style="text-align:right; ">245</td><td >FRANCE</td><td >Code and Name match</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">245</td><td >FRANCE</td><td style="text-align:right; ">4455</td><td >CANADA</td><td >Dont exists code</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">7899</td><td >EGYPT</td><td style="text-align:right; ">12345</td><td >ENGLAND</td><td >Code exists but differet name</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(IF(VLOOKUP(C2,$A$2:$B$4,2,0)=D2,"Code and Name match","Code exists but differet name"),"Dont exists code")</td></tr></table></td></tr></table>
 
Upvote 0
this is perfect. but now I realize I need to have this displayed in another way...not sure I can do it with formula.

Thanks for this though, gonna use it another time!
 
Upvote 0
this is perfect. but now I realize I need to have this displayed in another way...not sure I can do it with formula.

Thanks for this though, gonna use it another time!

That's right, at no time did you mention how you wanted the result, so the proposals may not be what you had in mind.
But if you put what you need, maybe ...
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top