Offset doesn't work with spilled Index,Match

[jarvisjaska]

I'm having a problem trying to get offset value with index,match. The following formulas both work fine:

=OFFSET(INDEX('Sheet1'!H:H;(MATCH(A7;'Sheet1'!C:C;0)));1;0)
=INDEX('Sheet1'!H:H;FILTER(MATCH($A:$A;'Sheet1'!$C:$C;0);ISNUMBER($A:$A)))

But when I use

=OFFSET(INDEX('Sheet1'!H:H;FILTER(MATCH($A:$A;'Sheet1'!$C:$C;0);ISNUMBER($A:$A)));1;0)

Excel returns #VALUE! -error.

I think this is because offset doesn't work on array, but is there any other way to do this?

 

[Fluff]

How about

=INDEX(Sheet1!H:H,FILTER(MATCH($A:$A,Sheet1!$C:$C,0)+1,ISNUMBER($A:$A)))

 

[jarvisjaska]

How about

=INDEX(Sheet1!H:H,FILTER(MATCH($A:$A,Sheet1!$C:$C,0)+1,ISNUMBER($A:$A)))

That did the trick, thank you!

Why didn't I think of that...

 

[Fluff]

You're welcome & thanks for the feedback.