Automatically Looking-Up Values on a Tab, finding a match and entering the findings on a

I need help with creating a formula that searches a TABLE on TAB-1, matches the headers there with headers on TAB-2 and then, concatenates the 3 rows below the matching header on TAB-1 in the list format on Tab-2.

I have attached a worksheet that best illustrates my challenge...

Although I have tried, I can't seem to find a graceful way of doing this through a formula…
So, would appreciate any help this Forum could give me with this.
JPAutomatically looking up Values.xlsx

Well, this is hardly a graceful formula, but with your list in a separate sheet starting at A3, you could use this formula in B3:

=IF(ISNA(MATCH(A3,CHALLENGE!B:B,0)),IF(ISNA(MATCH(A3,CHALLENGE!C:C,0)),INDEX(CHALLENGE!D:D,MATCH(A3,CHALLENGE!D:D,0)+1)&" - "&INDEX(CHALLENGE!D:D,MATCH(A3,CHALLENGE!D:D,0)+2)&" - "&INDEX(CHALLENGE!D:D,MATCH(A3,CHALLENGE!D:D,0)+3),INDEX(CHALLENGE!C:C,MATCH(A3,CHALLENGE!C:C,0)+1)&" - "&INDEX(CHALLENGE!C:C,MATCH(A3,CHALLENGE!C:C,0)+2)&" - "&INDEX(CHALLENGE!C:C,MATCH(A3,CHALLENGE!C:C,0)+3)),INDEX(CHALLENGE!B:B,MATCH(A3,CHALLENGE!B:B,0)+1)&" - "&INDEX(CHALLENGE!B:B,MATCH(A3,CHALLENGE!B:B,0)+2)&" - "&INDEX(CHALLENGE!B:B,MATCH(A3,CHALLENGE!B:B,0)+3))

Essentially, it says this:

Please Login or Register  to view this content.

It assumes that the headers in column A all exist within the table - you will get errors if they are invalid, so you would need to wrap:

IFERROR( ... ,"")

around the formula if that is a problem for you. Copy this down, as can be seen in the attached file.

Hope this helps.

Pete

Thank you Pete... your solution certainly works well on the example and is much appreciated; as I was beginning to believe this could not be done.
Now I need to think through how practical it may be to scale-up if the actual Table grows to 40+ headers with possibly a few blanks in-between.
Sigh... Thank you again for helping me.
JP

write at B17
=IFERROR(INDEX($B$3:$D$14,(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+1,MOD(ROW(1:1)-1,3)+1),"")
copy down

at C17
=IFERROR(INDEX($B$3:$D$14,(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+2,MOD(ROW(1:1)-1,3))&" - "&INDEX($B$3:$D$14,(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+3,MOD(ROW(1:1)-1,3)+1)&" - "&INDEX($B$3:$D$14,(TRUNC((TRUNC(ROW(1:1)-1)/3)+1)-1)*4+4,MOD(ROW(1:1)-1,3)+1),"")

copy down

Thank you Ghozi... wow, that is much simpler and it seems, more practical to "scale-up".
i am going to invest time to try and understand the step-by-step "spirit" of your formula but... perhaps you can help me fast figure out why the first "Info-1" row under each heading is picking-up the values from the next side row... I think it has to do with either the TRUNC or MOD numbers in the formula but, because I don't yet fully understand what is going on within the formula, I can't really tell...
Thank you very much for your consideration Ghozi.
JP

Hi Ghozi... just wanted to let you know that regarding my earlier comment;
"why the first "Info-1" row under each heading is picking-up the values from the next side row... I think it has to do with either the TRUNC or MOD numbers in the formula but, because I don't yet fully understand what is going on within the formula, I can't really tell"
I figured it out... we were missing "+1" in the second formula. :-)
Now it all works and scales-up perfectly.
Thanks again for you very good solution.

Hi Oeldere... thank you very much for taking the time and effort to help me figure this out.
Your consideration is much appreciated.
I have looked at your approach - which also works - but is in my opinion a bit more complex to scale-up.
I think Ghozi really came-up with the most elegant way to resolve my challenge... you may want to take a look at his approach for your reference.
Thank you again Oeldere.
With best regards... JP