index/match formula? comparing 4 columns

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,279
Office Version
2007
Platform
Windows
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>
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
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!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,279
Office Version
2007
Platform
Windows
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 ...
 

Watch MrExcel Video

Forum statistics

Threads
1,096,456
Messages
5,450,565
Members
405,618
Latest member
preethamdevraj

This Week's Hot Topics

Top