Data to fetch 2nd and 3rd occurance

[webmax]

Hi

I am attaching the sample template in that we have two sheets template and Data.

We have to fetch the measures from the data sheet using formula in the template

For Example for Emp ID 40100023 there are three measures to appear in the Template if type the employee code in the B1 Cell like wise for Emp ID 40100046 there are two measures like wise for Emp ID 40100053 Four Meausre.

Kindly provide the solution for the same.

Thanks & regards
Shahul

 

[Ramesh]

Have a look at the attached..where I have written an array formula

 

[webmax]

Hi Ramesh

Thanks for the formula can you explain what is the ROW()-6 you are using in the formula
Regards
Shahul

 

[Ramesh]

As we want all the rows in sequence, instead of hard coding the row numbers as 1,2,3 etc. I am taking the help of Row function.. the Row() function returns the current row number and as the data range starts from 1st row in the data sheet, in order arrive at the 2nd row and 3rd row, from the current row I am subtracting 6. In this case, the formula written at cell A8 shows the formula as Row()-6, where the Row() function returns 8 and subtracting 6 gives 2, which indicates that I am fetching the 2nd row value in my array. When it goes to A9, the Row()-6 gives 3 that means the 3rd value and so on.

Hope that explains!! sorry If I made it too complicated..