Excel Offset function

[dicken]

Hi,
if I want to use Offset but want to do this based on a value , so I want the cell reference containing X as my starting point,
I have tried ;

  CELL("address",INDEX($A$1:$A$13,MATCH(D1,$A$1:$A$13,0),1) )[ = $A$4

Which returns the correct cell reference but when I nest in Offset I get an error, so I'm either making a basic mistake or
I need a deferent approach?

Richard.

 

[6StringJazzer]

Your descriptions are not really communicating what you need. It might be straightforward in your own head but it is not coming across to us. Consider using XL2BB to show a worksheet range.

Looking at your example formula, which seems to be totally different than your first post:

MATCH("c",H7:H14,0)  - 2

So you are looking for the value "c" in the range H7:H14. You want to return the value that is 2 rows above that. You do this:

=INDEX(H5:H12,MATCH("c",H7:H14,0))

This assumes that 2 (for 2 rows above) is constant, not a variable in a cell. This could also be done with OFFSET, but I'm avoiding that for a simple case like this.

 

[Joe4]

6StringJazzer,

Like you, it is not quite clear to me and conditions seem to be shifting (a realistic example like we both asked for would be most helpful).
However, if you look at the formula they posted in reply 10, the OFFSET function is returning a multi-cell range (3 rows by 2 columns).
So that may be one reason why INDEX/MATCH won't work, if they are looking to return a 3x2 grid, and not a single cell.

 

[Alex Blakenburg]

Having had the benefit of the previous discussion I am wondering if it is worth zooming in on what you said works and seeing if that takes us anywhere.
Ignoring looking in multiple columns to find the address which might be worth a separate thread, in post #1 you said your index match returned the correct address and in post #10 you gave an example of an offset function you wanted to use.
Using the fact that INDEX and XLOOKUP return the actual Cell and not just the value in the cell, combining your post 1 & 10 info would give you this:

PS: I had to move the lookup value D1 in post 1 out of the return value range post 10, so it is in J1.

INDEX

=OFFSET(INDEX($A$1:$A$13,MATCH(D1,$A$1:$A$13,0),1),4,2,3,2)

XLOOKUP

=OFFSET(XLOOKUP(J1,$A$1:$A$13,$A$1:$A$13),4,2,3,2)

Book1
	A	B	C	D	E	F	G	H	I	J
1	11	21	31	41	51	61		Search Value --->		13
2	12	22	32	42	52	62
3	13	23	33	43	53	63		INDEX
4	14	24	34	44	54	64		=OFFSET(INDEX($A$1:$A$13,MATCH(J1,$A$1:$A$13,0),1),4,2,3,2)
5	15	25	35	45	55	65
6	16	26	36	46	56	66		37	47
7	17	27	37	47	57	67		38	48
8	18	28	38	48	58	68		39	49
9	19	29	39	49	59	69
10	20	30	40	50	60	70
11	21	31	41	51	61	71		XLOOKUP
12								=OFFSET(XLOOKUP(J1,$A$1:$A$13,$A$1:$A$13),4,2,3,2)
13
14								37	47
15								38	48
16								39	49
Sheet1

Cell Formulas
Range	Formula
H4,H12	H4	=FORMULATEXT(H6)
H6:I8	H6	=OFFSET(INDEX($A$1:$A$13,MATCH(J1,$A$1:$A$13,0),1),4,2,3,2)
H14:I16	H14	=OFFSET(XLOOKUP(J1,$A$1:$A$13,$A$1:$A$13),4,2,3,2)
Dynamic array formulas.