Using CELL to get the address of an INDEX, then using SUM OFFSET

[KansaiDorifto]

Hello

I have a number (e.g. $5,000) which I want to look up in a table, then I want to add it and the next two numbers next to it.

I have tried:
1) Using INDEX/MATCH to get the number
+INDEX($M$3:$AK$21, MATCH($M45, $M$3:$M$21, 0), MATCH(N$43, $M$3:$AK$3, 0)) -> which results in a number $5000
2) Using CELL/ADDRESS to get the cell location of that result
=CELL("address", INDEX($M$3:$AK$21, MATCH($M45, $M$3:$M$21, 0), MATCH(N$43, $M$3:$AK$3, 0)) ) -> which results in $N$8
3) But when I include it in a SUM/OFFSET formula, it generates an error:
+SUM(OFFSET(CELL("address", INDEX($M$3:$AK$21, MATCH($M45, $M$3:$M$21, 0), MATCH(N$43, $M$3:$AK$3, 0)) ), 0, 0, 1, 3)) -> error

I don't understand why it's causing an error, because it works when I manually type in $N$8:
+SUM(OFFSET($N$8, 0, 0, 1, 3))
and my step 2 should be returning exactly $N$8?

Appreciate any help on solving this, or a better formula to get this done.

Thanks!

 

[bebo021999]

Have you tried confirm with Ctrl-Shift-Enter, instead of Enter?
BTW, I believe there is other solution. Your solution is full of volatile functions those need to avoid using.
Share the sheet to get better solution, without volatile formula.

 

[KansaiDorifto]

Have you tried confirm with Ctrl-Shift-Enter, instead of Enter?
BTW, I believe there is other solution. Your solution is full of volatile functions those need to avoid using.
Share the sheet to get better solution, without volatile formula.

Hi bebo

Yes i have tried it, same error.

Unfortunately I cannot install the xl2bb tp upload the mini sheet since this is my work laptop. Here is an image of what i'm trying to do:

 

[bebo021999]

CELL function returns cell address, but in text string, not reference.
try put it inside INDIRECT() then it should works

Book1
	G	H	I	J	K	L
2						Output
3						241
4
5		aa	bb	cc	dd	ee
6	a	38	42	56	13	42
7	b	100	59	71	85	85
8	c	13	11	12	61	96
9	d	73	77	83	21	11
10	e	17	62	62	22	60
11	f	25	83	24	44	49
12	g	90	68	17	45	42
Sheet1

Cell Formulas
Range	Formula
L3	L3	=SUM(OFFSET(INDIRECT(CELL("address",INDEX($G$5:$L$12, MATCH("b", $G$5:$G$12, 0),MATCH("cc", $G$5:$L$5, 0)))),0,0,1,3))

 

[bebo021999]

With one more option (shorter) in Output2

Book1
	G	H	I	J	K	L	M
1
2						Output1	Output2
3						241	241
4
5		aa	bb	cc	dd	ee
6	a	38	42	56	13	42
7	b	100	59	71	85	85
8	c	13	11	12	61	96
9	d	73	77	83	21	11
10	e	17	62	62	22	60
11	f	25	83	24	44	49
12	g	90	68	17	45	42
Sheet1

Cell Formulas
Range	Formula
L3	L3	=SUM(OFFSET(INDIRECT(CELL("address",INDEX($G$5:$L$12, MATCH("b", $G$5:$G$12, 0),MATCH("cc", $G$5:$L$5, 0)))),0,0,1,3))
M3	M3	=SUM(OFFSET(G5,MATCH("b",$G$6:$G$12,0),MATCH("cc",$H$5:$L$5,0),,3))

 

[KansaiDorifto]

With one more option (shorter) in Output2

Book1
	G	H	I	J	K	L	M
1
2						Output1	Output2
3						241	241
4
5		aa	bb	cc	dd	ee
6	a	38	42	56	13	42
7	b	100	59	71	85	85
8	c	13	11	12	61	96
9	d	73	77	83	21	11
10	e	17	62	62	22	60
11	f	25	83	24	44	49
12	g	90	68	17	45	42
Sheet1

Cell Formulas
Range	Formula
L3	L3	=SUM(OFFSET(INDIRECT(CELL("address",INDEX($G$5:$L$12, MATCH("b", $G$5:$G$12, 0),MATCH("cc", $G$5:$L$5, 0)))),0,0,1,3))
M3	M3	=SUM(OFFSET(G5,MATCH("b",$G$6:$G$12,0),MATCH("cc",$H$5:$L$5,0),,3))

Thanks!