Getting relative value from match

[kilnakorr]

Hi

Very new to excel, and trying to get a value relative to match.
Example. If my value match cell H5, I need to get value two rows down and three columns right (K7)

Anyone who can give some pointers?

 

[6StringJazzer]

You left out a lot of information needed to give you an actual formula that will work for your specific situation.
As an example let's say you are searching for the value taken from cell A1, and you are searching for an exact match in column H:

=MATCH(A1,H:H,0)

If you want to return the value two rows down and three columns to the right, use INDEX:

=INDEX(H:K,MATCH(A1,H:H,0)+2,3)

However, since we know the answer will always be in column K, we can shorten it:

=INDEX(K:K,MATCH(A1,H:H,0)+2)

It would help to give all the details of what you are doing.

 

[kilnakorr]

You left out a lot of information needed to give you an actual formula that will work for your specific situation.
As an example let's say you are searching for the value taken from cell A1, and you are searching for an exact match in column H:

=MATCH(A1,H:H,0)

If you want to return the value two rows down and three columns to the right, use INDEX:

=INDEX(H:K,MATCH(A1,H:H,0)+2,3)

However, since we know the answer will always be in column K, we can shorten it:

=INDEX(K:K,MATCH(A1,H:H,0)+2)

It would help to give all the details of what you are doing.

Thank you.

Yes, I might have left out information, as I'm unsure what is needed.

I have tried using =INDEX(H:K,MATCH(A1,H:H,0)+2,3), however this does not work as needed. - It 'might' find the value 3 rows down, but the column is not relative to the match.

In short. The value I need to find is never in a specific row or column. So I will always need to find the row and column relative to the match.

My match value (a date) is always in row 5, but different column.

 

[6StringJazzer]

I have tried using =INDEX(H:K,MATCH(A1,H:H,0)+2,3), however this does not work as needed. - It 'might' find the value 3 rows down, but the column is not relative to the match.

I made one error. This formula will return the value that is 2 rows down, but the 3 means "the third column of the range where H is the first column". If you need to return the value 3 columns to the right, this number must be 4.

You need to provide a very concrete description of what you are doing. In what range are you searching for the value? What determines the number of rows and columns to offset to find the result? If the value you want to find is never in a specific row or column, MATCH isn't going to work. MATCH returns the position of a match within a single column, or a single row.

Here is an example of finding a number in a 2D array, and returning the number that is 2 rows down and 3 columns to the right.

$scratch.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1				Find number	22			1	16	31	46	61
2	7			rows down	2			2	17	32	47	62
3	2			columns to the right	3			3	18	33	48	63
4								4	19	34	49	64
5				Result	69			5	20	35	50	65
6								6	21	36	51	66
7								7	22	37	52	67
8								8	23	38	53	68
9								9	24	39	54	69
10								10	25	40	55	70
11								11	26	41	56	71
12								12	27	42	57	72
13								13	28	43	58	73
14								14	29	44	59	74
15								15	30	45	60	75
Sheet2

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT(($H$1:$K$15=$E$1)*ROW($H$1:$K$15))-@ROW($H$1:$K$15)+1
A3	A3	=SUMPRODUCT(($H$1:$K$15=$E$1)*COLUMN($H$1:$K$15))-@COLUMN($H$1:$K$15)+1
E5	E5	=INDEX($H$1:$L$15,$A$2+$E$2,$A$3+$E$3)
L1	L1	=K15+1
L2:L15	L2	=L1+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:L15	Expression	=H1=$E$5	text	NO
H1:K15	Expression	=AND(ROW(H1)=$A$2+ROW($H$1)-1,COLUMN(H1)=$A$3+COLUMN($H$1)-1)	text	NO

 

[kilnakorr]

You need to provide a very concrete description of what you are doing. In what range are you searching for the value? What determines the number of rows and columns to offset to find the result? If the value you want to find is never in a specific row or column, MATCH isn't going to work. MATCH returns the position of a match within a single column, or a single row.

I think that the main issue is that the info I need is not in a fixed column, which is why I need the relative position.
The info might be in column C one day, and next day column F and so on.

It seems there is no simple way to find a cell matching a given value, and just get the info located eg. two rows down and three columns right of that cell.

I cannot upload pictures here, without getting an 'oops something went wrong' error, so not sure how to show what I want to achieve.

 

[6StringJazzer]

Well, I showed you exactly how to do it, and though I wouldn't call it simple, it's not that hard. How is your situation different from my example?

 

[kilnakorr]

Well, I showed you exactly how to do it, and though I wouldn't call it simple, it's not that hard. How is your situation different from my example?

This will work with numbers, but I need to get text also.

 

[Peter_SSs]

This will work with numbers, but I need to get text also.

What is the difference?
In either case the solution relies on the value in E1 occurring just once in columns H:L

23 11 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1				Find value	v			a	p	ae	at	bi
2	7			rows down	2			b	q	af	au	bj
3	2			columns to the right	3			c	r	ag	av	bk
4								d	s	ah	aw	bl
5				Result	bq			e	t	ai	ax	bm
6								f	u	aj	ay	bn
7								g	v	ak	az	bo
8								h	w	al	ba	bp
9								i	x	am	bb	bq
10								j	y	an	bc	br
11								k	z	ao	bd	bs
12								l	aa	ap	be	bt
13								m	ab	aq	bf	bu
14								n	ac	ar	bg	bv
15								o	ad	as	bh	bw
Relative value

Cell Formulas
Range	Formula
A2	A2	=SUMPRODUCT(($H$1:$K$15=$E$1)*ROW($H$1:$K$15))-@ROW($H$1:$K$15)+1
A3	A3	=SUMPRODUCT(($H$1:$K$15=$E$1)*COLUMN($H$1:$K$15))-@COLUMN($H$1:$K$15)+1
E5	E5	=INDEX($H$1:$L$15,$A$2+$E$2,$A$3+$E$3)