OFFSET error

[chrono2483]

I keep getting a #VALUE! or #REF! error with my offset formula. I've followed the logic and it should point to the desired cell. It is suppose to find the name in column A, go down 5 rows and return 2 columns/cells of data.

=OFFSET(INDEX($A$1:$A$500,MATCH($I3,$A$1:$A$500,0)),5,,,2)


can anyone tell what I am doing wrong?

 

[Jonmo1]

The offset is returning a 2 cell range (columns A&B 5 rows below where the match was found)..
So you're trying to make 1 cell display the values of 2 cells. = #Value! error.

Offset CAN indeed return a multicell range, but you have to actually DO something with that range, like SUM it..
=SUM(OFFSET(INDEX($A$1:$A$500,MATCH($I3,$A$1:$A$500,0)),5,,,2))

 

[gaz_chops]

Index the column you want the result from
=OFFSET(INDEX($C$1:$C$500,MATCH($I3,$A$1:$A$500,0)),5,,,1)

 

[Jonmo1]

Looks like I understood the intention differently than gaz_chops did...

If your intention is to return the value from the cell 2 columns over (C is 2 columns over from A), and 5 rows down from the match..

Try like this to avoid the Volatile Offset function

=INDEX($C$6:$C$505,MATCH($I3,$A$1:$A$500,0))

Notice the 5 row offset from column A and C..

 

[gaz_chops]

Jonmo1, i've never thought of doing that! Brilliant.

 

[Jonmo1]

Jonmo1, i've never thought of doing that! Brilliant.

Thanks

Another common method I've seen is to just add 5 to the match..
=INDEX($C$1:$C$500,MATCH($I3,$A$1:$A$500,0)+5)

But that will result in #REF! if the match is found in A496+

 

[chrono2483]

Thank you both - I was able to revise and it was a success!!!

 

[Jonmo1]

Glad to help, thanks for the feedback.