Index Match

[StanSz]

I'm having trouble in getting the Index & Match combination to give me the results I want, obviously indicating that my formulas are incorrect.

When I use the formula =MATCH(A12,A11:A1000,0) to search for the position of the value in A12 within the range of A11:A1000, I get the correct answer of 2, since the A12 value is right at the top of the range.

When I use the formula =INDEX(A11:AB1000,2,25) to go to the second row down and 25 columns to the right in the range A11:AB1000 I get the desired answer of 1620.

However, when I try to combine the Match and Index arguments in the following manner =INDEX(A11:AB1000,2,25,MATCH(A12,A11:A1000,0)) I get a #REF error, when what I want to get is the answer 1620.

What I am doing incorrectly ?? I'm trying to use a value found in the A column in one location, match it in another range of varying values and return the desired offset value

Thanks

 

[Marcílio_Lobão]

StanSz, Good evening.

Have you tried to use:

=INDEX(A11:AB1000, MATCH(A12,A11:A1000,0) ,25)

Please, tell us if it worked for you.

I hope it helps.

 

[StanSz]

Dear Marcillo,

Yes, it does work, Thank you.

My question though is I don't see where the formula says to go to the 2nd row, and then 25 columns over, but somehow it does. If I wanted to go 3 rows down, or 7 rows, how would I adjust the formula ??

Stan

StanSz, Good evening.

Have you tried to use:

=INDEX(A11:AB1000, MATCH(A12,A11:A1000,0) ,25)

Please, tell us if it worked for you.

I hope it helps.

 

[Marcílio_Lobão]

Stan,

I colored BLUE the part that you automated the line number at the formula.

Remember the syntax of the INDEX function:

=INDEX(Array, Line Number, Column Number)

"...If I wanted to go 3 rows down, or 7 rows, how would I adjust the formula ??..."
Well, either you use the automatic line number parameter (the same as your formula at blue part is doing) or you enter the line number manually instead of the formula.

Was it possible to understand now how it works?

Please, any questions you can send message.
This is the perfect place to ask questions.

I hope it helps.

 

[StanSz]

The formula that works (thanks, again) is giving me the value 25 columns over in the same row number as the match.

But, if I wanted to have a Line Number OFFSET from the Match row ?? as in my initial Index formula of =INDEX(A11:$AB1000,2,25) -- which is 2 rows down within the range.

I'm wanting to go 2, or 3, or 7 row numbers from the Match row. Can I not say, "find the match row, and then give me the value 2 rows down from that, and 25 columns over" ??

Stan,

I colored BLUE the part that you automated the line number at the formula.

Remember the syntax of the INDEX function:

=INDEX(Array, Line Number, Column Number)

"...If I wanted to go 3 rows down, or 7 rows, how would I adjust the formula ??..."
Well, either you use the automatic line number parameter (the same as your formula at blue part is doing) or you enter the line number manually instead of the formula.

Was it possible to understand now how it works?

Please, any questions you can send message.
This is the perfect place to ask questions.

I hope it helps.

 

[Marcílio_Lobão]

Stan, Good evening.

To solve this question you need to change the result of your MATCH function.

Two ways:

a) Directly at the formula:

=INDEX(A11:AB1000, MATCH(A12,A11:A1000,0) + 2 ,25)


b) Using another cell:

D4 --> 2

=INDEX(A11:AB1000, MATCH(A12,A11:A1000,0) + D4 ,25)

Please, is that what you're looking for?

I hope it helps.

 

[StanSz]

This is exactly what I'm looking for -- once again Thank you. I didn't know where to put the row offset value.

Greetings from Santiago, Chile,

Stan

Stan, Good evening.

To solve this question you need to change the result of your MATCH function.

Two ways:

a) Directly at the formula:

=INDEX(A11:AB1000, MATCH(A12,A11:A1000,0) + 2 ,25)


b) Using another cell:

D4 --> 2

=INDEX(A11:AB1000, MATCH(A12,A11:A1000,0) + D4 ,25)

Please, is that what you're looking for?

I hope it helps.

 

[Marcílio_Lobão]

Stan,

Glad to have helped you.

Greetings from Belo Horizonte, Brazil.

Have a nice week!