# How to do this?

#### agonysWeeper

##### Board Regular
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

Last edited:
Hi agonysWeeper,

Try this:

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

HTH

Robert

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

Are you using 2007?

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)

<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>

Replies
16
Views
451
Replies
2
Views
188
Replies
7
Views
291
Replies
4
Views
204
Replies
2
Views
219

1,196,345
Messages
6,014,730
Members
441,842
Latest member
mattdale

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back