VLOOKUP or MATCH?

[Barryj]

I am trying to get cells populated with data from another sheet when a name is matched.

On sheet 1 I want to be able to type in a name in H9 and if that matches a name in sheet 2 column A then populate sheet 1 D4:U7 with Data from sheet2.

I Have included a file that I hope will help explain what I am trying to achieve.

So what I am basically trying to do is if I typed in Port Kembla into cell H9 on sheet 1 then the data in cells B4:S7 would be placed into cells D4:U7 on sheet 1

[mdmackillop]

Hi Barry,
Here's a code solution. Paste this into the Sheet1 code module

[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range

If Not Intersect(Target, [H9]) Is Nothing Then
Set Found = Sheets("Sheet2").Columns(1).Find(What:=Target, After:=[A1])
Range("D4:U7") = Found.Range("C1:T4").Value
End If
End Sub
[/VBA]

[Bob Phillips]

Formula solution.

Enter

=INDEX(Sheet2!C:C,MATCH($H$9,Sheet2!$A:$A,0)+ROW(A1)-1)

in D4 and copy across, and down.

[Barryj]

Thankyou both, with the Macro solution, if I want to add more names to the drop down list how would I do this and make the range on sheet 2 larger to expand as required,
is there also code required for the list box?

[mdmackillop]

Hi Barry,
It's simple and very useful. This dropdown is done with Data Validation. Select the cell and go to menu item Data/Validation. You'll see there how this is done. If you wish to keep your data on another sheet, you need to give the data list a range name eg Data, then you can refer to this name in the source box =Data.