Index/Match formula does not pick the correct records across lookup table

[skydivetom]

Experts:

I would like some assistance with modifying an Index/Match formula (or replacing w/ a different formula altogehter).

Based on a value of either "1", "2", or "3" I want to display either the "1st-level", "2nd-level" or "3rd-level" records from a lookup table. I included a helper column in my lookup table
which stores "1, 2, 3" values.

Please see more details below. Also, attached is my Excel example.

***************************

Background:
- Cell A1 is a drop-down with 3 values (1, 2, 3)
- Cell range B2:B4 is where I want to "store" data
- Cell range A9:B17 is my lookup area

Envisioned Process (when selecting, e.g., "1" in cell A1):
- I select, e.g., "1" in cell A1
- Cells B2:B4 should now display values = "Group A #1", "Group B #1", "Group C #1"
- So, based on the value = "1" (in cell A1"), I want to use the reference pointers in A9:A17 to pick data from B9, B12, and B15 and place them into B2:B4

Envisioned Process (when selecting, e.g., "2" in cell A2):
- I select, e.g., "2" in cell A1
- Cells B2:B4 should now display values = "Group A #2", "Group B #2", "Group C #2"
- So, based on the value = "2" (in cell A1"), I want to use the reference pointers in A9:A17 to pick data from B10, B13, and B16 and place them into B2:B4
- ... so forth with "3" (or "4" if I were to extend my lookup table to include Group D, etc.)

Current Issue:
- When selecting "1" in cell A1, I get all "Group A" values in B2:B4 (based on existing INDEX/MATCH formula)
- Alternatively, when selecting "2" in cell A1, I get a "mix" of "#2", "#3", and "1" records. Ultimately, based on the existing formula, it does NOT appear to skip 3 rows
- However, instead I want to show only the records labeled with "Group _ #1" (based on the lookup table)

My question:
-How should I modify the formula in B2:B4 to pick either all "Group _ #1" OR "Group _ #2" OR "Group _ #3" records?

Note:
Also, please keep in mind that I will have also data in, e.g., column "C". I'm sure that should be straight-forward as I would only have to replace the lookup range from B9:B17 to C9:C17 for formulas in C2:C4, right?

***************************

Thank you for your help in advance,
EEH

[Bo_Ry]

Please try at B2

=IFERROR(INDEX(B$9:B$17,AGGREGATE(15,6,ROW($A$9:$A$17)/($A$9:$A$17=$A$1),ROWS(B$2:B2))-ROW($A$8)),"")

[skydivetom]

Bo_Ry -- your solution -- AS ALWAYS -- is absolutely perfect. I already expanded it to include a "fourth" group as well as data in column C. It works perfectly!!!

Just one quick question... what does the "Aggregate" component in the formula work? Specifically, what's the meaning of the "15, 6..."? If you're too busy, not to worry... I'm just curious.

Again, THOUSAND THANKS!!!

[Bo_Ry]

Please check this
https://support.microsoft.com/en-us/...rs=en-us&ad=us
15 for small
6 ignore error

The whole concept.
https://youtu.be/mkQhBBE1PqQ

[skydivetom]

Awesome... I'll read up on it and check out the video. Appreciate your help!!!

Have a great weekend.