In this post I am going to tell you a useful excel formula for merchandisers, planners and buyers ( or every garment professional) which is going to save you a lot of time and will let your boss wondering about your speed and efficiency.

Many a times garment professionals have to copy a style information from one excel sheet to other. To do this, the normally adopted method is to open both the sheets, search the style number , select the style colour then copy the relevant data and paste it into the other sheet.

This is a time taking and painful process and I have seen people spending 4-5 hours to do this. How would you react if I tell you that you can do this just by writing one formula? Yes it is possible and all the time that you have spent in copy pasting can be saved.

This can be done by Using the:

Index and match function in excel.

Because one style number can be repeated depending upon the number of colours therefore we cannot use Vlookup. We have to lookup values based on two parameters. One is style number and other is the style colour both these things combined will make a style unique.

An index and match function can give us lookup values based on two parameters.

Let us understand this function by a simple example.

Copying specific style data from one table to another in same sheet.

In the above image we have 4 styles and we have to copy the updated SMV from table 1 to table 2. We can do this by writing the following formula in cell “O4”.

=INDEX($E$4:$E$7,MATCH(1,(K4=$C$4:$C$7)*(L4=$D$4:$D$7),0))

Note – After writing the formula press Ctrl+Shift+enter instead of only enter because this is an array formula. If not, this formula will give an #NA error.

 Where :

E4:E7  is the range of cells that you want to return a value from (in this case updates SMV). 

K4 is style number of the particular style (table 2)

C4:C7 is the range in the first table where style number is entered in table 1

L4 is color for the particular style (table 2)

D4:D7 is the range in the first table where style colour is entered in table 1

 

How to lookup values from one sheet to another using index and match.

Now that we have learned to use the index and match in the same sheet. The same formula can be used to copy values from one sheet to another as well as one workbook to another.

Just select the proper values as shown in the formula above and press CTRL+SHIFT+ENTER.

Download link to excel containing formula

Below is the link to download the excel sheet with a formula to copy style data from worksheet to another such that understanding of the formula becomes easier

download excel template for Index and match using two criteria

Supplementary reading on the topic

For better understanding of the function please read the following links

INDEX & MATCH functions in Excel – a better alternative to VLOOKUP

About the Author :

I am Varun Sanadhya with a keen interest in research & development for garment manufacturing. I love to explore the abilities of MS excel and have developed a number of helpful advanced excel softwares for the industry.  You could write to me for help/enquiries regarding excel formats at leanstitchtalk@gmail.com