Need help aligning a table to a column based on similar values in one table field

Question

I am needing to align a table containing a column 'reference' in excel with another column 'reference' with more data entries than the table has based on duplicate values. I am having a hard time doing this with MATCH and INDEX. This was possible for only two columns but I am not sure how to do it with an entire table. Example attached as image.

I tried using MATCH and INDEX to align the two columns in a separate column but could not get that to work. just got #reference. Necessary example image

ID	Reference	Misc		reference
3	apple	a		orange
7	banana	b		grape
10	apricot	c		banana
12	green bean	d		apple
				blueberry
				strawberry
				green bean
				apricot
				strawberry

(the second reference column is not part of the table, the first 3 columns are part of a table.

I would like the result to look similar to this, below.
Where all values are in a table together, and the references are aligned based on duplicate values.

ID	Reference	Misc	Reference
			orange
			grape
7	banana	b	banana
3	apple	a	apple
			blueberry
			strawberry
12	green bean	d	green bean
10	apricot	c	apricot
			strawberry

=IF(ISNA(MATCH(B2,$E2:$E$30256,0)),"",INDEX($B$2:$E$30256,MATCH(B2,$E2:$E$30256,0),2))

This is the formula that I tried originally with the real table. The real reference column in the table has 6000 entries and the column on its own has 30,000

Answer 1

This is a SPILL formula in a cell

=LET(base,E2:E9,
look,B2:B5,
table,A2:C5,
render,IF(ISNUMBER(MATCH(base,look,0)),CHOOSEROWS(table,IFERROR(MATCH(base,look,0),1)),""),
HSTACK(render,base))

adjust the base, look and table range to the actual values.

Answer 2

If I understand your scenario correctly, you can match your values using XLOOKUP and repeat this across columns. For your purposes, this functions like an INDEX-MATCH, but is easier to use, and the XLOOKUP also contains lookup direction (e.g. top down...) and a condition for not found.

The formula in a given row for each column should all reference the "reference" column. And, if you use an excel table, the range does not need updating.

I.e.

ID

=XLOOKUP([@reference2],Table2[Reference],Table2[ID],"",0)

Reference

=XLOOKUP([@reference2],Table2[Reference],Table2[Reference],"",0)

Misc

=XLOOKUP([@reference2],Table2[Reference],Table2[Misc],"",0)

Result

Answer 3

Try the following:

=IFNA(INDEX(B$2:D$5,MATCH($I2,$C$2:$C$5,0),),"")

(you'll need to adjust the ranges to match your tables)