How to compare 3 columns using excel 2003

[ksnr2006]

Hi All,

I have data in 3 columns like company id in Server 1(Column A), company id in server 2(Column B), and company name in server 2(Column C),.

If the value of company id server 1 is matched to any one of the value in company id in server 2, the formula should display the respective company id and company name.

Please help me.

Thank you in advance.

Satya.example(1).xls

[benishiryo]

hi Satya. try this in D2:
=IF(COUNTIF($B$2:$B$8,A2),A2,"Not found")

and this in E2:
=IF(D2="Not Found","NA",VLOOKUP(A2,$B$2:$C$8,2,0))

[ksnr2006]

I have tried but it is not working.

I am getting all NAs in E column.

[ksnr2006]

Hi benishiryo ,

Your formula is working for the example data which I have given but in actual file I have 1800 values in Column A, 3447 values in column B and C. Also, Some of the company ids are alpha numeric in Column B and some are blank but have company names.

Could you please check and provide me the formula for this kind of data.

Thank you.

[Kevin UK]

Hi ksnr2006

Another alternative:
In D2 & copy down: =IF(COUNTIF($B$2:$B$8,A2),A2,"Not Found")
In E2 & copy down: =IFERROR(INDEX($C$2:$C$8,MATCH(D2,$B$2:$B$8,0)),"NA")