How can I make Offset work when referencing a Table in an INDEX/MATCH formula?

[KirnonBhale]

I currently have the following formula:

=INDEX(DetList[Department],MATCH($C$4,OFFSET(DetList[Service],2,0),0))

This formula however isn't returning the desired result. The OFFSET isn't functioning. I don't know how to achieve my desired result and am hoping someone here can help me out?

Regards.

 

[Special-K99]

In words what are you trying to do?

Are you trying to MATCH C4 against the values 2 columns to the right of the column called Service in the DetList table?

Cant you just create a Named range of the column which is 2 to the right of DetList([Service] and remove the OFFSET altogether?

 

[KirnonBhale]

Hey Special-K99,

Thanks, actually while trying to answer your request for further info, you enabled me to figure out what I was doing wrong!! Essentially I was trying to offset the row 2 points down and return the answer from that point. what I needed to do was also offset the DetList[Department] the to the same level to achieve the result.

Formula now is:
=INDEX(OFFSET(DetList[Department],2,0),MATCH($C$4,OFFSET(DetList[Service],2,0),0)) and this has done the trick.

Thanks, I know you didn't answer the question but your prompt for clarity helped me realise what I was doing wrong.

<colgroup><col></colgroup><tbody>
</tbody>

 

[Special-K99]

Oh glad I could help (if I did)

 

[KirnonBhale]

Oh glad I could help (if I did)

you helped, by getting me to think a bit more. On further investigation I hit on an even better solution.

This Array formula worked amazingly well and is by far and away the best option.

{=IFERROR(INDEX(DetList[Department],SMALL(IF(DetList[Service]=$C$4,ROW(DetList[Service])-ROW(INDEX(DetList[Service],1,1))+1),COUNTA($Q$6:Q$6))),"")}