Urgent , How to search data. pls help

[pol]

Hi ,
I have two sheets in a file,Sheet1.Datalist, Sheet2.Mapping.

Sheet Datalist contains two column.

Itemcode, Rate. Profit
------------------------------
AAAA 12.50
CCCC 4.50
DDDD 11.50
EEEEE 12.75
FFFFF 09.25

Sheet Mapping contain

Code Profit
--------------------
CCCC 2.50
DDDD 12.30
XXXX 10.50
FFFF 09.25
AAAA 00.25

I have to find each code of datalist in mapping sheet and if found there ,
the corresponding profit should be written in datalist sheet on the profit
column.

Please help to write a macros or help with any other option in excel to
find out the solution.

With many thanks

Pol

 

[Jim Thomlinson]

Entered in Cell C2 of the Datalist Sheet

=index('Mapping'!B$2:B$100, match($A2, 'Mapping'!$A$2:$A$100, 0))

 

[Bernard Liengme]

With your data for DataList having AAAA in cell A2, and in Mapping having
CCCC in A2
In Datalist cell C2 use =VLOOKUP(A2,Mapping!A:B,2,FALSE)
But if a match is not found this returns #N/A
To have a blank returned
in Excel 2003 (and before):
=IF(ISNA(VLOOKUP(A2,Mapping!A:B,2,FALSE)),"",VLOOKUP(A2,Mapping!A:B,2,FALSE))
in Excel 2007+ use: =IFERROR(VLOOKUP(A2,Mapping!A:B,2,FALSE),"")
best wishes

 

[Bernard Liengme]

Sample file at my account on Microsoft's Skydrive site
http://cid-162119ff97286948.skydrive.live.com/self.aspx/.Public/26April2010B.xls