copy specific cells from sheets if the information is correct

[mr_shocker2002]

Dear anyone that can help. i am new to functions and formulas in excel
and only really use it in a basic way.

what i have is the following, two sheets with huge amounts of data o
cells.

A1 through to O1 has information on a piece of equipment.
A2 through to O2 is the same piece of equipment with different seria
number.
A3 through to O3 is a different piece of gear

A1 is AMP1001, A2 is Amp1002 A3 is AMP2001

on another sheet i have more information that i wish to put on the firs
sheet

A1 is AMP1 i need the infomration from D1 through to M1 copied to th
first sheet to P1 through to z1
of course A2 needs the same information from the first cells.

A3 needs to copy the information from second sheet A2.

is there a formula that can lookup A1 on the first sheet, and if th
information on the second sheet has part of the same text (AMP1 or AMP2
will copy to the first sheet?

i hope this makes sense.

thanks in advance for your help, it is very much appreciated

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

attached is the dummy workbook

on the second sheet, this is the information i want to copy, i hav
highlighted this in yellow.

on the main sheet this is where the information need to be copied to.

as you can see in the workbook, the green is the information that coul
be used for the formula., the only difference is, on the second shee
this is an item code, where as on the main sheet it is an item code wit
item number.

i have four of these workbooks to compile, and each one has thousands o
rows. manually is going to take me all week! and i am going to ge
crosseyed....

thanks again for your assistance, you are saving me hours and hours o
work.

Hi,

Still struggling to understand exactly what you need here.

If you look up values from second tab into the first, would cells Q2, Q
& Q4 all have the same value

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[mr_shocker2002]

Hi,

Still struggling to understand exactly what you need here.

If you look up values from second tab into the first, would cells Q2, Q
& Q4 all have the same value?

What i need to do is for Q2 through to AB2 to copy the data from th
second sheet of d2 through toO2.

but then on the q3 it would need to copy the same data.

when we get to q5 it would need to copy the data from d3

so AMP1 information on the data sheet neeeds to be copied to rows 2,3,
and AMP2 information gets copied to rows 5,6,7,

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

ok. got it now
=VLOOKUP(LEFT($A2,4),'Test Sheet'!$A$3:$O$6,4,FALSE)

cheers

Could you not have just used the values in column B on each sheet as th
lookup value?

That's what I was going to suggest previously, but it seemed like to
obvious an answer to have been overlooked already.

From the sample data it would give the same results as yours

+-------------------------------------------------------------------
+-------------------------------------------------------------------