Need Help in Auto fill - would Vlookup be better or Macro

[Excelnoob01]

I have a data sheet where i need data from sheet 1 column H to look up for data on sheet 2 in coloumn C and find the matching value and fill the data from rows adjcent to Column C into Sheet 1, Column J and K

Basically its should be like the below concept

Sheet 1

REF

DOB

Quantity

Pckg

Total

Discount

Net Amount

Code

Name

Comp

Distry

Sign

X11

16 jul 2015

2

499

998

0

998

CON111

874

<tbody>
</tbody>

Sheet 2

Name	Company	Code	Date
Charles	Duckling Inc	CON111

<tbody>
</tbody>

How can i have a formula or code that can auto fill this into sheet one once i put it under the code column ?
I want Charles and Duckling to be filled up automatically into Sheet 1

 

[Stubble Kid]

A VLookup would work but only if the Code column in the second sheet was the first column (ie your fields were Code, Name, Company, Date). VLookup can only look forwards from the lookup column, not backwards.

If you don't want to re-arrange the sheet 2 data you can use the following formula:


=IFERROR( INDIRECT("Sheet2!R" & MATCH(H2, Sheet2!C:C ,0) & "C1" ,FALSE),"")


You will have to modify it so that "Sheet2!R" shown in red refers to your sheet 2 name but with !R appended to the end.

The range shown in green (H2) will be the reference to the Code column in sheet 1.

The range shown in blue Sheet2!C:C can be changed to refer to whatever the range is for the Code values on sheet 2.

Reference to "C1" will return the value in the first column in sheet 2 (Name) and can be changed to the Company name by using "C2" to refer to column 2.

The formula can be copied or autofilled down the data as needed.