Index Match w/ Offset

[NatKV]

Hello!

My data spits out all wonky and therefore my index match formula requires some kind of offset.

Column H has the match cell, I need the formula to look at the A:A index, find the ID then move 4 rows down, 7 columns across (Yes exactly this, to retrieve that yellow highlighted amount.
I've also uploaded a file. Thank you!!

 

[bosco_yip]

In I8, copied down :

=IF(H8="","",INDEX(INDEX(G$6:G$50,MATCH(H8,A$6:A$50,0)):G$50,MATCH(1,INDEX(0+(INDEX(G$6:G$50,MATCH(H8,A$6:A$50,0)):G$50<>""),0),0)))

Regards
Bosco

 

[NatKV]

Hello Bosco!

This works!! This also looks like the craziest excel formula I have ever seen and I'm sitting here dissecting it slowly. Thank you very much.

 

[AliGW]

Cross-posted: https://www.excelguru.ca/forums/showthread.php?9261-Index-Match-w-Offset-Riddle-me-this!

 

[bobhc]

If you had taken just a moment to read the forum rules you would have read this bit,

• Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out.
• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
• Please don't attach or post links to copyright material including Books, Videos etc. Your post will be deleted

Members are giving their time for free to help you unaware that members of other forums may have given you an answer.