Look Up or Maybe IF formula

[Guest]

Hi there

I am trying to create a formula that looks in cell A1 in sheet B for a
matching value in A. When it finds the value in sheet A, I want it to copy
the next 10 cells in the corresponding row into cells B1, C1 etc...

Any advice appreciated.

Thanks

 

[T. Valko]

One way:

Select the 10 cell range B1:K1

Enter this array formula** :

=OFFSET(Sheet1!A1,MATCH(A1,Sheet1!A1:A10,0)-1,1,,10)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Don Guillett]

One way

Sub copymatchingline()
For Each c In Range("b5:b6") 'destination range
With Sheets("sheet2")
ma = .Range("a2:a22").Find(c).Address
.Range(ma).Resize(1, 10).Copy c
End With
Next c
End Sub