Display records on different sheets using vlookup

[justmine584]

Hi,
I have a problem that i want to display searched record on different sheet. Below are the details.

-I have 2 sheets named sheet1 & sheet2
-Sheet1 contain records of students like Age,Name,marks etc

Age Name Marks
10 A 60
11 B 60
11 C 30
9 D 50
10 E 40

I want to display records search condition on Age. Means on sheet2 if user enter any age in A1 cell then search data from sheet1 should be displayed on sheet2 cells (starting from D4 to ...). Record should be displayed at any location where formula will set by user. Column titles also different on both sheets.



Please help me guys. I need urgent solution.

 

[John Davis]

You did good work with pivot Table but requirement is that it should be done with vlookup. can it be done with vba? Is it possible.

Perhaps for VBA something like this?

Sub justmine584()
Dim x As Variant
Dim lr As Long
Dim lr2 As Long

lr = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row

Sheets("Sheet1").Range("B5:C" & lr2).Delete shift:=xlUp


x = InputBox("Please Enter the Post Code")

Sheets("Sheet1").Range("B3").Value = x

Sheets("Sheet3").Activate

    With Range("A2:C" & lr)
    
        .AutoFilter Field:=1, Criteria1:=x
        
    End With
    
    With Range("B2:C" & lr)
    
        .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet1").Range("B5")
    
    End With
    
    Cells.AutoFilter


End Sub