Match Offset Return Row number

[amolvijay]

Hi Experts,
I am looking for a formula to return row number to automate Index formula.
Cell E1 gives me result "6". However in formula I wish +2 should be automated.

Formula should first look for value in column D in column A, then it should look for subsequent "Total" in column B and return appropriate value against "Total".

	A	B	C	D	E
1	ABCD	Pen	3
2		Pencil	3
3		Total	6
4	XYZ	Pen	4	ABCD	=INDEX(C:C,MATCH(D4,A:A,0)+2)=6
5		Pencil	3	XYZ	=INDEX(C:C,MATCH(D5,A:A,0)+3)=9
6		Eraser	2
		Total	9

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe

E1 copied down
=IF(D1="","",INDEX(INDEX(C:C,MATCH(D1,A:A,0)):C$100000,MATCH("Total",INDEX(B:B,MATCH(D1,A:A,0)):B$100000,0)))

Hope this helps

M.

 

[amolvijay]

Thank you Marcelo...worked like charm

Maybe

E1 copied down
=IF(D1="","",INDEX(INDEX(C:C,MATCH(D1,A:A,0)):C$100000,MATCH("Total",INDEX(B:B,MATCH(D1,A:A,0)):B$100000,0)))

Hope this helps

M.

 

[amolvijay]

HI Marcelo,

Formula is not working if Cell "D1" is on Sheet2 of the workbook, and formula referenced to data on Sheet1

Can you please help?

Maybe

E1 copied down
=IF(D1="","",INDEX(INDEX(C:C,MATCH(D1,A:A,0)):C$100000,MATCH("Total",INDEX(B:B,MATCH(D1,A:A,0)):B$100000,0)))

Hope this helps

M.

 

[Marcelo Branco]

Not clear.
Where are the values ABCD, XYZ that you showed in column D (post 1)? Sheet1 or Sheet2?
Is the first formula in E1 of sheet2?

M.

 

[amolvijay]

I am trying to create a summary on sheet 2. So need to shift formula in E1 to Sheet2.

Updated formula as follows

=INDEX(INDEX(Sheet1!C:C,MATCH(D6,Sheet1!A:A,0)):C$100000,MATCH("Total",INDEX(sheet1!B:B,MATCH(D6,Sheet1!A:A,0)):B$100000,0))

which is not working. Please can u help.

Not clear.
Where are the values ABCD, XYZ that you showed in column D (post 1)? Sheet1 or Sheet2?
Is the first formula in E1 of sheet2?

M.

 

[Marcelo Branco]

Try (corrections in blue)
=INDEX(INDEX(Sheet1!C:C,MATCH(D6,Sheet1!A:A,0)):Sheet1!C$100000,MATCH("Total",INDEX(Sheet1!B:B,MATCH(D6,Sheet1!A:A,0)):Sheet1!B$100000,0))

If it doesn't work, try to provide data sample in Sheet1 and in Sheet2

M.