copy data from list based on text value in column A

try this
note: ` signifies a seperation of cells

sheet 1 data
``a``````b```````c
1`ar1```500````xyz1
2`ar2```600````xyz2
3`ar5```900````xyz3

sheet 2 data
``a``````b
1`ar6```=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A1,Sheet1!$A$1:$A$3,0),1)
2`ar5```=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A2,Sheet1!$A$1:$A$3,0),1)
3`ar2```=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A3,Sheet1!$A$1:$A$3,0),1)

formula for C1
=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A1,Sheet1!$A$1:$A$3,0),2)
formula for d1
=INDEX(Sheet1!$A$1:$C$3,MATCH(Sheet2!A1,Sheet1!$A$1:$A$3,0),3)

sheet 2 data RESULTS
``a``````b`````c`````d
1`ar6```#N/A``#N/A``#N/A
2`ar5```ar5```900```xyz3
3`ar2```ar2```600```xyz2

notice that in cells a1, a2, a3, the data matches and returns the result only if it matches exactly, otherwise, it will give you an #N/A result. As for the formatting, you will need to figure out the best format to use for your report.

cheers,

Thomas

(you might also want to learn Vlookup. Vlookup would work also.)

Thanks thomas for the formulas and hints