Offset spilled formula almost works... but not

[ED38]

Hi,
debuging a formula, i realized the following formula looks like doing the job (when I select it and fly over with the mouse) whereas when entered separeately in a cell, a list of value is shown:

EE5# is a vertical list of numbers

Could anyone help to understand the issue here?
thank you

 

[Fluff]

What exactly are you trying to do?

 

[Rick Rothstein]

Besides @Fluff's question, I would note that not one cell referenced in your formula (perhaps with the exception of the table reference, but that is not clear) is shown in your picture, so there is no way for us to test anything for you.

 

[ED38]

Hi both, thank you for offering your help.
let me try to explain:
for each value in the EE5# list (vertical), I need to find the matching value in the cost v0,95 tab : seqrching in col 'O' and get the matching value in column 'R'

2024-07-30 Tier pricing revenue impact analysis-LRC specific-cost v0.95.xlsx
	O	P	Q	R
25	VU	VUH	Tenant profiles per quantity
26		0		$0
27		1		$0
28	1	30	Nano	$100
29	30	501	Small	$500
30	101	5001	Medium	$1 500
31	9999999	9999999	Large	$3 000
Cost v0,95

If it may impacct the way to build this formula, this one is a portion of a larger spilled formula...

thank you

 

[Fluff]

Maybe

=XLOOKUP(EE5#,'cost v0,95'!O28:O31,'cost v0,95'!R28:R31,"",-1)

 

[ED38]

Once again Thank you Fluff, xlookup is a better option and solve my issue.

However, for my understanding and "excel culture", would you know why my formula did not work and why the expected result was shown when flying over the formula but deliver a set of 'value#' error in fact?
Have a nice day.

 

[Fluff]

I don't think that Offset can handle an array.

 

[ED38]

Hi Fluff,

Just to let you know I successfully used 'offset' in array mode. Here are 2 samples in the red cell for reference. It do work correctly
if I can help in turn!

Book5
	A	B	C	D	E	F	G	H	I	J
1
2
3
4					VDS Cumul Disc.	Total price	Required intermediate calc for SLAB (incremental value)
5	1	24 960	0	1	0	24 960	24 960			0
6	11	249 600	10	11	0,0%	274 560	249 600			1
7	12	20 592	11	12	17,5%	295 152	20 592			10
8	26	288 288	25	26	17,5%	583 440	288 288			11
9	27	16 848	26	27	32,5%	600 288	16 848			12
10	51	404 352	50	51	32,5%	1 004 640	404 352			25
11	52	13 728	51	52	45,0%	1 018 368	13 728			26
12	81	398 112	80	81	45,0%	1 416 480	398 112			27
13	82	11 232	81	82	55,0%	1 427 712	11 232			50
14	10 000	########	9999	10 000	55,0%	112 826 687	111 398 975			51
15										52
16										80
17										81
18										82
19										9999
20										10000
Sheet1

Cell Formulas
Range	Formula
C5:C14	C5	=D5#-1
D5:D14	D5	=A5:A14
F5:F14	F5	=SUBTOTAL(9,OFFSET(G5#,0,0,ROW(D5#)-ROW(D$5)+1,))
G5:G14	G5	=B5:B14
J5:J20	J5	=SORT(UNIQUE(VSTACK(D5#,OFFSET(D5#,0,-1,,))))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B14	Expression	=B5=FALSE	text	NO
A5:A14	Expression	=A5=FALSE	text	NO
F5:F14	Expression	=F5=FALSE	text	NO
D5:E14,G5:G14	Expression	=D5=FALSE	text	NO

 

[Fluff]

D5# & G5# are ranges, not an arrays. That's why it works.

 

[ED38]

My confusion!
Thank you again Fluff.