=MATCH(A1,H:H,0)
=INDEX(H:K,MATCH(A1,H:H,0)+2,3)
=INDEX(K:K,MATCH(A1,H:H,0)+2)
Thank you.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:
Excel Formula:=MATCH(A1,H:H,0)
If you want to return the value two rows down and three columns to the right, use INDEX:
Excel Formula:=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:
Excel Formula:=INDEX(K:K,MATCH(A1,H:H,0)+2)
It would help to give all the details of what you are doing.
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.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.
$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 |
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.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.
This will work with numbers, but I need to get text also.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?
What is the difference?This will work with numbers, but I need to get text also.
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) |