How to do this?

[agonysWeeper]

sheet1 A1-A16 (List of the Name)
sheet1 D1-D16 (formula I am looking for)

sheet2 A1-A16 (List of the Name)
sheet2 H1-H16 (amount)

in the sheet1 column D example row 1 I want to find the same name in sheet2 then get the amount.

 

[repairman615]

Try:

Placed in D1 then filled down.

<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=296><COLGROUP><COL style="WIDTH: 222pt; mso-width-source: userset; mso-width-alt: 5412" width=296><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 222pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2636104 class=xl63 height=20 width=296>=VLOOKUP(A1,Sheet2!A1:D16,4,FALSE)</TD></TR></TBODY></TABLE>

edit: (FYI) Trebor76's formula will fill down better than this one...Use the $$ signs.

 

[Trebor76]

Hi agonysWeeper,

Try this:

=VLOOKUP(A1,Sheet2!$A$1:$H$16,8,FALSE)

HTH

Robert

 

[agonysWeeper]

Thanks, its working, I am looking now for a formula cause once A1 to 16 has no Name the column D showing the #N/A

 

[repairman615]

Are you using 2007?

 

[T. Valko]

Thanks, its working, I am looking now for a formula cause once A1 to 16 has no Name the column D showing the #N/A

Since you're returning numeric values if you use SUMIF rather than a lookup you'll get a result of 0 rather than the #N/A error.

=SUMIF(Sheet2!A$1:A$16,A1,Sheet2!H$1:H$16)

 

[repairman615]

<TABLE style="WIDTH: 639pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=852><COLGROUP><COL style="WIDTH: 96pt" width=128><COL style="WIDTH: 543pt; mso-width-source: userset; mso-width-alt: 13238" width=724><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2636398 class=xl65 height=20 width=128>2007</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 543pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl63 width=724>=IFERROR(VLOOKUP(A1,Sheet2!$A$1:$H$16,8,FALSE),"")


</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=21>2003</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl64>=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$H$16,8,FALSE)),"",VLOOKUP(A2,Sheet2!$A$1:$H$16,8,FALSE))</TD></TR></TBODY></TABLE>