Index/Match/Offset

[MrWoods1990]

Good Morning All!

I believe I need to use an Index/Match/Offset Feature in my sample sheet attached but I do not understand how to build it and I request your aid in this.

In the sample attached, Sheet X1 is my master copy of information which in the live version is updated record by record which is four lines in total and each record has its own reference number.

In sheet Look Up Search, I want to be able to type in a reference number in the in H4 and it return the information from any four lines linked to that reference number in Sheet One

In the Look Up Search I am having difficulty in replicating Row 12 from sheet X1.

If the reference number was to change, for example to 1 of 29/11/17 then I would like Rows 17 to 20 of X1 to be replicated in Look Up Search rows 9 to 12.

I hope I have given enough information for you to help me

Thank you in advance.

[FlameRetired]

Try entering this formula in A9 of 'Look Up Search' and fill across to S12.

Please Login or Register  to view this content.

[MrWoods1990]

Try entering this formula in A9 of 'Look Up Search' and fill across to S12.

Please Login or Register  to view this content.

This works perfectly and I have successfully managed to transfer it to the live version.

I beg one more question of you, could you break down the forumla for me and explain how it works, as I've really been trying to understand it but I can never find a explanation relevant to my work.

I would really appreciate it if you could do that for me.

[FlameRetired]

I'll do my best.

Using the Evaluate Formula feature under the FORMULAS ribbon and using the F9 function key will be helpful. Also examine the syntax of INDEX and MATCH.

If you are not familiar with INDEX its row_num and column_num references define the intersections of the range in the first argument array and return what it finds.

To define the row_num MATCH matches the location of H4 in range 'X1'!$A$9:$A$24 and returns the row number where the value of H4 is found. In this case there needs to be a further adjustment step to return the correct row of X1Info. That is done by 1.) subtraction 1 from what MATCH returns and 2.) then adding the rows count as you copy/fill the formula down.

In the formula bar select just the MATCH($H$4,'X1'!$A$9:$A$24,0) and hit the F9 function key. In the case of 1 of 29/11/17 it will return 9. Now include the -1 and F9ing shows 8. At this point ROWS($A$9:$A9) will be returning 1. This will be important for incrementing the row_num as you fill down. It assures that the row_num will always be pointing to the correct row.

The column_num specified by COLUMNS($A1:A1) is very simply returning the cumulative column counts ... 1 to n.

Together they start returning the upper left most data in the desired section of X1Info.

You can see exactly the order in which Excel calculates by selecting just one of the formula cells and then clicking Evaluate Formula. Continue clicking the Evaluate button. With each click Excel shows step by step how it solves the equation.

F9 and Evaluate Formula are helpful tools for trouble shooting, analyzing formulas and self instruction. I recommend them whenever I can. They are well worth getting familiar with.

Did this help?

I hope this helps.

[MrWoods1990]

I'll do my best.

Using the Evaluate Formula feature under the FORMULAS ribbon and using the F9 function key will be helpful. Also examine the syntax of INDEX and MATCH.

If you are not familiar with INDEX its row_num and column_num references define the intersections of the range in the first argument array and return what it finds.

To define the row_num MATCH matches the location of H4 in range 'X1'!$A$9:$A$24 and returns the row number where the value of H4 is found. In this case there needs to be a further adjustment step to return the correct row of X1Info. That is done by 1.) subtraction 1 from what MATCH returns and 2.) then adding the rows count as you copy/fill the formula down.

In the formula bar select just the MATCH($H$4,'X1'!$A$9:$A$24,0) and hit the F9 function key. In the case of 1 of 29/11/17 it will return 9. Now include the -1 and F9ing shows 8. At this point ROWS($A$9:$A9) will be returning 1. This will be important for incrementing the row_num as you fill down. It assures that the row_num will always be pointing to the correct row.

The column_num specified by COLUMNS($A1:A1) is very simply returning the cumulative column counts ... 1 to n.

Together they start returning the upper left most data in the desired section of X1Info.

You can see exactly the order in which Excel calculates by selecting just one of the formula cells and then clicking Evaluate Formula. Continue clicking the Evaluate button. With each click Excel shows step by step how it solves the equation.

F9 and Evaluate Formula are helpful tools for trouble shooting, analyzing formulas and self instruction. I recommend them whenever I can. They are well worth getting familiar with.

Did this help?

I hope this helps.

It is a very difficult formula in my opinion, I will try the evaluation method as well.

Thank you for all your help.