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 on cells.

A1 through to O1 has information on a piece of equipment.
A2 through to O2 is the same piece of equipment with different serial 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 first sheet

A1 is AMP1 i need the infomration from D1 through to M1 copied to the 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 the 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.

Hi,

Happy to help but any chance you could provide at least a dummy workbook?

attached is the dummy workbook

on the second sheet, this is the information i want to copy, i have 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 could be used for the formula., the only difference is, on the second sheet this is an item code, where as on the main sheet it is an item code with item number.

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

thanks again for your assistance, you are saving me hours and hours of 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, Q3 & Q4 all have the same value?

What i need to do is for Q2 through to AB2 to copy the data from the 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,4 and AMP2 information gets copied to rows 5,6,7,8

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 the lookup value?

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

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

I was going to do the Column B, until i got about a third of the way down and we had duplicate desriptions for differing item codes. The only standard is the item codes and item numbers, all the rest of the data is a mish-mash of people entering in whatever they like.

thanks for your assistance, it is appreciated.

Thought that might be the case. Glad it's sorted now :)